sql - SQL Server 表中无数据时显示空值

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  |

标签： sqlsql-serversql-server-2008database-table

Answer 1

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'