Combine multiple columns from multiple rows with unique id?

August 22, 2023

I am trying to figure out how to create a query that will take the data below, which has a UniqueId column, and name value pairs for the additional columns and display it like the second table, but can’t seem to get it right. What my data looks like:

UniqueId    ColName    ColValue    UpdateTimestamp
123         Col1       ValueA      2023-08-15T11:25:14-01:00
123         Col2       ValueB      2023-08-15T11:25:14-01:00
123         Col3       ValueC      2023-08-15T11:25:14-01:00
456         Col1       ValueD      2023-08-15T11:25:14-01:00
456         Col2       ValueE      2023-08-15T11:25:14-01:00
456         Col3       ValueF      2023-08-15T11:25:14-01:00
789         Col1       ValueG      2023-08-15T11:25:14-01:00
789         Col2       ValueH      2023-08-15T11:25:14-01:00
789         Col3       ValueI      2023-08-15T11:25:14-01:00

What I want it to look like with my query:

UniqueId    Col1    Col2    Col3
123         ValueA  ValueB  ValueC
456         ValueD  ValueE  ValueF
789         ValueG  ValueH  ValueI

How can I make the data look like the table above with a select?

>Solution :

Try this:

SELECT
    UniqueId,
    MAX(CASE WHEN ColName = 'Col1' THEN ColValue END) AS Col1,
    MAX(CASE WHEN ColName = 'Col2' THEN ColValue END) AS Col2,
    MAX(CASE WHEN ColName = 'Col3' THEN ColValue END) AS Col3
FROM
    <YourTableName>
GROUP BY
    UniqueId;