Geek asks and answers

Combining two selected queries from one table

The table contains an identifier column, a valueHeading column, and a value column. I want to split the column of values ​​into two new columns named valueHeading1 and valueHeading2 depending on what type of valueHeading has a value.

So, I want to join this selection: Edit: Full connection

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

Use this to select:

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

in their respective identifiers. How to do it?

Edit to illustrate what I want to do:

Source 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
+5
source share
4 answers

In SQLServer2005 + it is possible to 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

+3
source

- :

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')

, :

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

. SQLFiddle. Oracle 11g MSSQL 2012, .

+5

If you only need to join, use this. Use the case when it is an elegant way, if you do not need a connection.

SELECT * FROM 
    (SELECT ID
          ,valueHeading
          ,value as 'valueHeading1'
    FROM table1
    WHERE valueHeading = 'valueHeading1') AS TAB_1, 
    (SELECT ID
          ,value as 'valueHeading2'
    FROM table1
    WHERE valueHeading = 'valueHeading2') AS TAB_2
    WHERE TAB_1.ID = TAB_2.ID
+4
source

self join can be a simple solution

SELECT DISTINCT t1.ID, t1.value as valueHeading1, t2.value as valueHeading2, 
FROM table1 t1
INNER JOIN table1 t2 ON t1.ID = t2.ID
WHERE t1.valueHeading <> t2.valueHeading
0
source

More articles:


All Articles