Kevin Stembridge Kevin Stembridge - 1 year ago 50
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download