mdc mdc - 1 month ago 8
SQL Question

Joining two select queries from the same table

The table contains an ID column, valueHeading column and a value column. I want to separate the value column into two new columns called valueHeading1 and valueHeading2 depending on which type of valueHeading the value has.

So I want to join this select:
Edit: Full join

SELECT ID
,valueHeading
,value as 'valueHeading1'
FROM table1
WHERE valueHeading = 'valueHeading1'


With This select:

SELECT ID
,value as 'valueHeading2'
FROM table1
WHERE valueHeading = 'valueHeading2'


on their respective ID's. How do I do this?

Edit to illustrate what I want to do:

Original table:

ID valueHeading value
0 valueHeading1 a
0 valueHeading2 a
1 valueHeading1 ab
1 valueHeading2 NULL
2 valueHeading1 abcd
2 valueHeading2 abc


New Table:

ID valueHeading1 valueHeading2
0 a a
1 ab NULL
2 abcd abc

Answer

In SQLServer2005+ possible use PIVOT

SELECT ID, valueHeading1, valueHeading2
FROM
(
 SELECT *
 FROM dbo.test28
 WHERE valueHeading IN ('valueHeading1', 'valueHeading2')
 ) x
 PIVOT
 (
  MAX(value)
  FOR valueHeading IN ([valueHeading1], [valueHeading2])
  ) p

Demo on SQLFiddle