Menu
I need to write a sql query on the table such that the result would have the group by column along with the aggregated column with comma separators.
My table would be in the below format
|`````````|````````|
| ID | Value |
|_________|________|
| 1 | a |
|_________|________|
| 1 | b |
|_________|________|
| 2 | c |
|_________|________|
Expected result should be in the below format
|`````````|````````|
| ID | Value |
|_________|________|
| 1 | a,b |
|_________|________|
| 2 | c |
|_________|________|
983
In order to fetch the comma separated (delimited) values from the Stored Procedure, you need to make use of a variable with data type and size same as the Output parameter and pass it as Output parameter using OUTPUT keyword.
The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
We can use GROUP BY to group together rows that have the same value in the Animal column, while using COUNT() to find out how many ID's we have in each group. It returns a table with three rows (one for each distinct animal).
You want to use FOR XML PATH construct:
select
ID,
stuff((select ', ' + Value
from YourTable t2 where t1.ID = t2.ID
for xml path('')),
1,2,'') [Values]
from YourTable t1
group by ID
The STUFF function is to get rid of the leading ', '.
You can also see another examples here:
192
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
