i have a table like:
ID value
3 1
3 2
3 3
3 4
3 5
3 6
Can anyone help me to convert this table into format:
ID Value
3 1,2,3,4,5,6
Asked
Active
Viewed 129 times
-3
Panagiotis Kanavos
- 120,703
- 13
- 188
- 236
WNQs
- 1
- 1
-
Google `GROUP_CONCAT` + the name of your RDBMS. – Martin Smith Jul 12 '13 at 07:28
-
1MySQL, MSSQL, PostgreSQL, anything else? – Maxim Zhukov Jul 12 '13 at 07:29
-
That's not row data, that's a concatenation. Also, group_concat is a MySSL statement not available on SQL Server – Panagiotis Kanavos Jul 12 '13 at 07:29
-
What have you tried already? Can we see some of the SQL code you have written to try and solve this? – Josien Jul 12 '13 at 07:30
1 Answers
1
select distinct id,
stuff((select (','+[value]) from Table1 as t2 where t1.id=t2.id
for xml path('')),1,1,'') as [values]
from
Table1 as t1
PS: in example scheme there is varchar() type for values, you will need to convert numeric to varchar() if needed.
There are plenty variants to make group_concat in Sql Server, just another way with assembly: GROUP_CONCAT string aggregate for SQL Server. I found it pretty nice working.
Another way with COALESCE
DECLARE @values varchar(100)
SELECT @values = COALESCE(@values + ', ', '') + [value]
FROM Table1
SELECT @values
revoua
- 2,044
- 1
- 21
- 28
-
Does this come from this previous answer ? http://stackoverflow.com/questions/111341/combine-multiple-results-in-a-subquery-into-a-single-comma-separated-value – Panagiotis Kanavos Jul 12 '13 at 07:33
-
-
Then it's customary to put a link to the previous SO answer – Panagiotis Kanavos Jul 12 '13 at 07:37
-
Another way to flag question as duplicate. Just I couldn't chose and answered directly. – revoua Jul 12 '13 at 07:39
-
Hmm, will be quite hard to define 'the previous SO answer' I think... Or could we write a query to determine it? – Josien Jul 12 '13 at 07:39