Kevin Stembridge Kevin Stembridge - 2 months ago 6
SQL Question

In SqlServer, how to join a single column from multiple rows into a single row with multiple columns

In SqlServer 2008, I have a table like this:

+--------+--------------------+--------------+
some_key | some_discriminator | some_column
-------- | ------------------ | -------------
K1 | D1 | V1
K1 | D2 | V2
K2 | D1 | V3
K2 | D2 | V4


and I'd like to run a query that produces this:

+--------+------+--------
some_key |D1 | D2
---------|---------------
K1 | V1 | V2
K2 | V3 | V4

Answer

A simple pivot query might suit your needs:

SELECT some_key,
       MAX(CASE WHEN some_discriminator = 'D1' THEN some_column ELSE NULL END) AS D1,
       MAX(CASE WHEN some_discriminator = 'D2' THEN some_column ELSE NULL END) AS D2
FROM yourTable
GROUP BY some_key

The grouping works because MAX ignores NULL values and MAX of a single VARCHAR returns that string.

But this might not be suitable if you have many columns, or dynamic columns, which you want to appear in the pivoted result.

Comments