2

I have table and query to select data from row to column like this:

 id  |  type    | data
-----------------------
  1  |  Name    | John
  1  |  Gender  | Male
  1  |  Code    | 1782
  2  |  Name    | Dave
  2  |  Gender  | Male

Query:

select a.id, a.data as [Name], b.data as [Gender], c.data as [Code]
from table1 a join table1 b on a.id = b.id
              join table1 c on b.id = c.id
where a.type = 'Name' and b.type = 'Gender' and c.type = 'Code'

Result:

  id |  Name   | Gender | Code  
 ------------------------------
  1  |  John   |  Male  | 1782

In this case id number 2 with the name 'Dave' doesn't have a 'Code' so it wont appear in the result. How can I still display the result with empty data or NULL on the 'Code' table so it will have result like this:

  id |  Name   | Gender | Code  
 ------------------------------
  1  |  John   |  Male  | 1782 
  2  |  Dave   |  Male  |
Dale K
  • 25,246
  • 15
  • 42
  • 71
Reus ellmy
  • 81
  • 6

3 Answers3

2
select a.id, a.data as [Name], b.data as [Gender], c.data as [Code]
from table1 a
left join table1 b on a.id = b.id and b.type='Gender'
left join table1 c on b.id = c.id and c.type='Code'
where a.type = 'Name'
Bart
  • 124
  • 7
  • 1
    It is correct according to the question, but when empty value or null in the middle the rest of the data become null too. – Reus ellmy Nov 16 '21 at 15:27
2

You can use CASE expression instead of JOINs :

SELECT
  a.id,
  MAX(CASE WHEN a.type = 'Name' THEN a.data ELSE '' END) AS [Name],
  MAX(CASE WHEN a.type = 'Gender' THEN a.data ELSE '' END) AS [Gender],
  MAX(CASE WHEN a.type = 'Code' THEN a.data ELSE '' END) AS [Code]
FROM table1 a
WHERE
    a.type IN('Name', 'Gender', 'Code')
GROUP BY a.id
iSR5
  • 3,274
  • 2
  • 14
  • 13
1

use a pivot query

select *
from   table1
       pivot
       (
           max(data)
           for type in ([Name], [Gender], [Code])
       ) p

demo

Squirrel
  • 23,507
  • 4
  • 34
  • 32