时间戳

首页 > 解决方案 > 将查询组合成一行

sql - 将查询组合成一行

问题描述

我正在运行的 SQL 查询返回重复的行，类似于以下内容：

id    | value_1 | value_2 | value_3
31443 | 1       | NULL    | NULL
31443 | NULL    | 3       | NULL
31443 | NULL    | NULL    | 5

我希望我的查询输出返回的是：

id    | value_1 | value_2 | value_3
31443 | 1       |       3 | 5

这就是我的查询当前的工作方式：

SELECT 
    id.[identifier] as 'id',
    (SELECT c.[value_1] WHERE c.[value_type_id] = '3') as 'value_1',
    (SELECT c.[value_2] WHERE c.[value_type_id] = '4') as 'value_2',
    (SELECT c.[value_3] WHERE c.[value_type_id] = '5') as 'value_3'
FROM Client as c
INNER JOIN Identity as id ON id.[id] = c.identity

标签： sqltsql

解决方案

看起来你实际上在这里是一个PIVOT/Cross 选项卡：

SELECT 
    id.[identifier] AS id,
    MAX(CASE c.[value_type_id] WHEN 3 THEN c.[value_1] END) AS value_1,
    MAX(CASE c.[value_type_id] WHEN 4 THEN c.[value_2] END) AS value_2,
    MAX(CASE c.[value_type_id] WHEN 5 THEN c.[value_3] END) AS value_3,
FROM dbo.Client AS c
INNER JOIN dbo.[Identity] AS id ON id.[id] = c.[identity] --IDENTITY is a reserved keyword, I suggest using a different name here
GROUP BY id.[identifier];

推荐阅读