mdc mdc - 10 months ago 41
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

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

With This select:

,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 Source

In SQLServer2005+ possible use PIVOT

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

Demo on SQLFiddle