当表上没有数据时显示空值

Question

我有一个表和查询来从一行到另一列选择数据，如下所示：

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

查询：

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'

结果：

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

在这种情况下，名为"Dave"的id号2没有"代码"，因此它不会出现在结果中。我怎么还能在"代码"表上显示空数据或NULL的结果，这样它就会有这样的结果：

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

Answer 1

您可以使用CASE表达式而不是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

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

Answer 3

使用透视查询

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

演示