I'm kinda stuck in writing Stored Procedure. Here is the case. I have one table, below is the illustration
| Name | Score |
| A | 10 |
| A | 20 |
| A | 30 |
| B | 20 |
| B | 50 |
And i'm trying to get a result as follows from the Stored Procedure
| Name | Scores |
| A | 10,20,30 |
| B | 20,50 |
Is it possible to get such a result from a SQL Query or Stored Procedure ? How ?
You can create a CLR user-defined aggregate:
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
[Serializable()]
[SqlUserDefinedAggregate(
Format.UserDefined,
IsInvariantToNulls=true,
IsInvariantToDuplicates=false,
IsInvariantToOrder=false,
MaxByteSize=8000)]
public class Concat : IBinarySerialize
{
#region Private fields
private string separator;
private StringBuilder intermediateResult;
#endregion
#region IBinarySerialize members
public void Read(BinaryReader r)
{
this.intermediateResult = new StringBuilder(r.ReadString());
}
public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
#endregion
#region Aggregation contract methods
public void Init()
{
this.separator = ", ";
this.intermediateResult = new StringBuilder();
}
public void Accumulate(SqlString pValue)
{
if (pValue.IsNull)
{
return;
}
if (this.intermediateResult.Length > 0)
{
this.intermediateResult.Append(this.separator);
}
this.intermediateResult.Append(pValue.Value);
}
public void Merge(Concat pOtherAggregate)
{
this.intermediateResult.Append(pOtherAggregate.intermediateResult);
}
public SqlString Terminate()
{
return this.intermediateResult.ToString();
}
#endregion
}
and use it in a query like any other aggregate function:
SELECT Name, dbo.Concat(Score) AS Scores
FROM dbo.Table
GROUP BY Name
The article A SQL CLR user-defined aggregate - notes on creating and debugging posted on my blog contains a detailed explanation of this code.
In this case COALESCE is your friend. I'm not a COALESCE expert, I just know it works so you may want to look it up if you want to dig deeper.
The snippet below will get you one row at a time, given the name to look for in @Name, I'd turn this into a function in SQL server, then call that function in your upper level SP somewhere, word of caution though: If you have NULL values in your result set, it will cause you to get incorrect results.
DECLARE @Name varchar
SET @Name = 'A'
DECLARE @Row varchar(max)
SELECT
@Row = COALESCE(@Row + ', ','') + CAST(Score AS varchar)
FROM
sotest2
WHERE
name = @Name
SELECT @Name,@Row
'sotest2' is the table name by the way, that was just what I called it in my db :-)
Revised after considering comments:
I recommend reading Rob Farley's excellent blog post Handling special characters with FOR XML PATH(''). His solution will allow for string concatenation of your grouped ID's without concern for fields that might have special characters.
DECLARE @t TABLE (Name CHAR(1), Score INT)
INSERT @t VALUES
('A', 10),
('A', 20),
('A', 30),
('B', 20),
('B', 50)
SELECT
STUFF(
(SELECT ', ' + CONVERT(VARCHAR(10), Score)
FROM @t
WHERE Name = t.Name
ORDER BY Score
FOR XML PATH(''),
TYPE).value('(./text())[1]','varchar(max)'),
1, 2, '') AS Score
FROM @t t
GROUP BY Name
The quirky update method:
DECLARE @DistinctName TABLE
(
Name VARCHAR(10) PRIMARY KEY
);
INSERT @DistinctName (Name)
VALUES ('A'),('B');
DECLARE @Test TABLE
(
TestId INT IDENTITY(1,1) PRIMARY KEY
,Name VARCHAR(10) NOT NULL
,Score INT NOT NULL
,Result VARCHAR(1000) NOT NULL DEFAULT ''
);
INSERT @Test (Name, Score)
VALUES ('A',10),('A',20),('A',30),('B',40),('B',50);
DECLARE @OldName VARCHAR(10) = ''
,@IsNewGroup BIT = 1
,@Concat VARCHAR(1000);
WITH SourceCTE
AS
(
SELECT TOP(1000) t.*
FROM @Test t
ORDER BY t.Name
)
UPDATE SourceCTE
SET @IsNewGroup = CASE WHEN @OldName <> Name THEN 1 ELSE 0 END
,@OldName = Name
,@Concat = Result = CASE WHEN @IsNewGroup = 1 THEN '' ELSE @Concat END + ',' + CAST(Score AS VARCHAR(10))
--OUTPUT inserted.Name, inserted.TestId , inserted.Result
SELECT dn.Name
,SUBSTRING(ca.Result,2,1000) Result
FROM @DistinctName dn
CROSS APPLY
(
SELECT TOP(1) Result
FROM @Test t
WHERE t.Name = dn.Name
ORDER BY t.TestId DESC
) ca;
Mentions:
