This is done with a pivot table. Grouping by id
, you issue CASE
statements for each value you want to capture in a column and use something like a MAX()
aggregate to eliminate the nulls and collapse down to one row.
SELECT
id,
/* if col1 matches the name string of this CASE, return col2, otherwise return NULL */
/* Then, the outer MAX() aggregate will eliminate all NULLs and collapse it down to one row per id */
MAX(CASE WHEN (col1 = 'name') THEN col2 ELSE NULL END) AS name,
MAX(CASE WHEN (col1 = 'name2') THEN col2 ELSE NULL END) AS name2,
MAX(CASE WHEN (col1 = 'name3') THEN col2 ELSE NULL END) AS name3
FROM
yourtable
GROUP BY id
ORDER BY id
Here’s a working sample
Note: This only works as is for a finite and known number of possible values for col1
. If the number of possible values is unknown, you need to build the SQL statement dynamically in a loop.