How to transpose a table in sql?

Question

  a    | b     |
+------+-------+
| 1    | a,b,c |
| 1    | d,e,f |
| 1    | g,h   |
+------+-------+

I would want output like below with a sql script

1 , a,b,c,d,e,f,g,h

Answer 1

I'm assuming you're using sql-server for this, but you can modify the query to work for MySQL.

This one is a little tough, but you can use the STUFF argument to concatenate the strings. It'll come out with as a query similar to this:

SELECT 
[a],
  STUFF((
    SELECT ', ' + [b]
    FROM #YourTable 
    WHERE (a = 1) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
    ,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY [a]

Basically, you use the STUFF argument to iterate through the rows where a=1 in your table and concatenate them with a comma and space in-between each value for the [b] column. Then you group them by the [a] column to prevent you from returning one row for each original [a] row in the table.

I encourage you to check out this post for credits to the query and other possible solutions to your answer.

Answer 2

For Postgres you can use:

select a, string_agg(b::text, ',' order by b)
from the_table
group by a;