sql table from set of rows to one row

Question

My problem is straight forward:

i keep trying to transform the output of a table from

1 a
1 b     into    1 a b c
1 c

2 d
2 e     into    2 d e f
2 f

The table will have always sets of 3 rows that will become a table with 4 columns like above.

I tried looping and got stuck…

EDIT: is for SQL_SERVER

What RDBMS? my_SQL, SQL_SERVER? Oracle? – xQbert Jul 01 '13 at 15:45 — xQbert, Jul 01 '13 at 15:45

xQbert · Answer 1 · 2013-07-01T15:56:43.787

0

For MY_SQL

SELECT A, group_concat(B SEPARATOR " ") as BResult
FROM TABLE
GROUP BY A

For ORACLE Look at List_Agg or WM_CONCAT or over order by if order of B is important.
For SQL SERVER look at for XML Path.

However if you're simply trying to transform rows to columns...

you can use a cte if your dbms supports it. you can look at pivots. if your rdbms supports it.

edited Jul 01 '13 at 15:56

answered Jul 01 '13 at 15:47

xQbert

34,733
2
41
62

im using sql server...i'll try your solution to see if it works – Nathan Jul 01 '13 at 15:52
it won't the case operator assumed like data or some sort of type to define which data when to which column. Wihtout it there's no way to make it work. – xQbert Jul 01 '13 at 15:58
To make it work you'd have to use a pseudo_column like Row_Number and order by A then B and then somehow evaluate rows 1,4,7,10 etc (+3 each time) and then 2, 5,8,11 (+3 each time) and then ... if no type exists for each separate row. – xQbert Jul 01 '13 at 16:07

score 0 · Answer 2 · edited May 23 '17 at 12:21

You can use FOR XML PATH construct to emulate MySQL's GROUP_CONCAT like this:

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

sql table from set of rows to one row

2 Answers2