One of the most common questions I receive in email is how to group multiple columns data in comma separate values in a single row grouping by another column.
I have previously blogged about it in following two blog posts. However, both aren’t addressing the following exact problem.
- Comma Separated Values (CSV) from Table Column
- Comma Separated Values (CSV) from Table Column – Part 2
The question comes in many different formats, but in following image I am demonstrating the same question in simple words. This is the most popular question on my Facebook page as well. (Example)
Here is the sample script to build the sample dataset.
CREATE TABLE TestTable (ID INT, Col VARCHAR(4)) GO INSERT INTO TestTable (ID, Col) SELECT 1, 'A' UNION ALL SELECT 1, 'B' UNION ALL SELECT 1, 'C' UNION ALL SELECT 2, 'A' UNION ALL SELECT 2, 'B' UNION ALL SELECT 2, 'C' UNION ALL SELECT 2, 'D' UNION ALL SELECT 2, 'E' GO SELECT * FROM TestTable GO
Here is the solution which will build an answer to the above question.
-- Get CSV values
SELECT t.ID, STUFF(
(SELECT ',' + s.Col
FROM TestTable s
WHERE s.ID = t.ID
FOR XML PATH('')),1,1,'') AS CSV
FROM TestTable AS t
GROUP BY t.ID
GO
I hope this is an easy solution. I am going to point to this blog post in the future for all the similar questions.
Final Clean Up Act
-- Clean up DROP TABLE TestTable GO
Here is the question back to you –
Is there any better way to write above script? Please leave a comment and I will write a separate blog post with due credit.
Reference:Â Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
56 Comments. Leave new
i have :
tabel A : id || name ==> A1, A2, A3 || AZ, AX, XX
tabel B : idd || nameB ==> B1, B2, B3, B4 || BQ, BV, BC, BB
tabel C : A_ID || B_ID ( foreign key from tabel A and B ) ==> A1, A1, A2, A2, A3 || B2, B1, B1, B3, B4
I want the result like this :
name || nameB
AZ || BV, BQ
AX || BQ, BC
XX || BB
This is my query, but name column won’t come out :
SELECT A.NAMA, STUFF (
(select ‘ , ‘ + B.NIT_ID
FROM JTAB B
WHERE J.NIK_ID = B.NIK_ID
FOR XML PATH(”)),1,2,”) AS RES
FROM Ahli A, TRAINING T, JTAB J
WHERE J.NIK_ID = A.NIK AND J.NIT_ID = T.NIT
GROUP BY J.NIK_ID, A.NAMA
help me please ….
How to use more than one AND operator on same field?
in mysql is having simple function for this like Group_concat() in sql server its very big syntax to write..
Thanx for the above query!! :)
i want output like this
ID CSV
1 A
B
C
2 A
B
C
D
E
CSV is Multiline Column.
Please reply…
THanx in Advance.
It would be nice if MS SQL supports group_concat:
SELECT t.id, GROUP_CONCAT(t.col)
FROM TestTable t
GROUP BY t.id
Great Idea! Please put your feature request to Microsoft via https://docs.microsoft.com/en-us/collaborate/connect-redirect
is it possible with charindex() function
My question is
this my string –> SQL Server
and i want output like-> SQL
Server
using charindex()
you want to “replace” a space with new line. Not sure if charindex would help.
thank you sir
Welcome.
a function that will accept a string as input parameter and will encrypt the data as explained below –
1. replace each alphabet within this string with it’s corresponding reverse alphabet
• A with Z, B with Y, C with X …so on
2) Similarly replace numbers – 0 with 9, 1 with 8, 2 with 7, 3 with 6, 4 with 5, 5 with 4, 6 with 3, 7 with 2, 8 with 1 & 9 with 0
3). All characters other than alphabets & numbers and having ASCII code less than 128 should be replaced by corresponding Extended ASCII characters. (Extended code = ASCII code of current character + 128)
it would be easy if you can create a mapping table and use that rather than complex query.
sir will you explain brifely please
Q)a stored procedure that will accept two input string parameters and will encrypt the data as explained below –
First parameter should be the message to encrypt and second parameter should be the key for encryption. The encryption key must be min. 6 to 8 characters mix of alphabets & numbers. Procedure should perform encryption by adding the key to message. In addition to this, the procedure should also decrypt back the message using same key.
I can only give you pointers. you need to write your own code Prashant.
I’ve used this methodology before… very nice! Quick question – how would I add in the ID field to the CSV output field.
Ex: CSV column output = Row 1: 1,A,B,C…. Row 2: 2,A,B,C,D,E…. etc.
I can get it in my CSV output, but it repeats like so: 1,A,1,B,1,C…
Any help is greatly appreciated!
Can you post sample DDL and data insert statements. Also shared expected output.
I having scenario as follows
Col1 Col2 Col3
A 1 2
A 3 4
B 5 6
solution as follows which should have better performance
Col1 Col2 Col3
A 1,3 2,4
B 5 6
Denomination
0.01,0.02,0.05,0.10,0.20,0.50,1,2,5,10,20,50
1,2,5,10,20,50,100
0.5,1,2,5,10,20,50,100
0.5,1,2,5,10,20,50,100
50,100,200,500,1000
50,100,200,500,1000
1,5,10,20,50,100
In the above Denomination column i want to get the smallest of each row. Please can any help on this.
For example 1st row should return 0.01, 2nd row should return 1 ………
Its very difficult to find an article u r searching for in this blog. I searched Group By , it gave me 81 pages of results. 2 years back this was my favorite SQL site, I used to suggest to my friends about this site,I searched for 30 min in 81 pages then I had no patience I moved on to another SQL site.
This is really unexpected from SQL authority site.
Sometimes my returns have multiple concatenated numbers but some of the numbers are the same. How is that happening?
Hi I have to do the same thing in SQL.
I have question regarding your code:
what is t.id, s.id and s.col?
Thank you Pinal. It helps!!