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
Use the UNION ALL clause to join data from columns in two or more tables. In our example, we join data from the employee and customer tables. On the left of the UNION ALL keyword, put the first SELECT statement to get data from the first table (in our example, the table employee ).
You can think of a self join as a join between two copies of the same table. For each record with a non-null value in spouse_id , we search for the value of customer_id that matches it. When we find a match, the columns firstname and lastname are added to the resulting table. Table aliases are required in a self join.
Try something like :
SELECT ID
, CASE WHEN valueHeading = 'valueHeading1' THEN value ELSE NULL END AS valueHeading1
, CASE WHEN valueHeading = 'valueHeading2' THEN value ELSE NULL END AS valueHeading2
FROM table1
WHERE valueHeading IN ('valueHeading1', 'valueHeading2')
If you want to regroup all values on one row for each ID, you can try :
SELECT ID
, MAX(CASE WHEN valueHeading = 'valueHeading1' THEN value ELSE NULL END) AS valueHeading1
, MAX(CASE WHEN valueHeading = 'valueHeading2' THEN value ELSE NULL END) AS valueHeading2
FROM table1
WHERE valueHeading IN ('valueHeading1', 'valueHeading2')
GROUP BY ID
HAVING MAX(CASE WHEN valueHeading = 'valueHeading1' THEN value ELSE NULL END) IS NOT NULL
OR MAX(CASE WHEN valueHeading = 'valueHeading2' THEN value ELSE NULL END) IS NOT NULL
See SQLFiddle. I also tried on Oracle 11g and MSSQL 2012, and it works each time.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With