Joining two select queries from the same table

join, select, sql, sql-server

Solution

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

Problem

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 ```

Original source