sql—尝试将多个键id组合到一行中，但列中的值不同

wa7juj8i 于 2021-08-01 发布在 Java

关注(0)|答案(3)|浏览(271)

tsql-sql服务器
我正在根据非常具体的要求编写一份报告。我试图将一个键id的倍数合并到一行中，但是在某些列中有不同的值，所以groupby不起作用。

SELECT count(tt.Person_ID) as CandCount, tt.Person_ID, 
CASE e.EthnicSuperCategoryID WHEN CandCount > 1 THEN 10 ELSE e.EthnicSuperCategoryID END as EthnicSuperCategoryID,
CASE e.Ethnicity_Id WHEN 1 THEN 1 ELSE 0 END as Black ,
CASE e.Ethnicity_Id WHEN 2 THEN 1 ELSE 0 END as White ,
CASE e.Ethnicity_Id WHEN 3 THEN 1 ELSE 0 END as Asian,
etc
FROM T_1 TT
JOINS
WHERE
GROUP

消息102，级别15，状态1，第4行“>”附近语法不正确。
以下是结果（没有第一个案例）。注：第三人称有多种种族。

SELECT count(tt.Person_ID) as CandCount, tt.Person_ID, 
CASE e.Ethnicity_Id WHEN 1 THEN 1 ELSE 0 END as Black ,
CASE e.Ethnicity_Id WHEN 2 THEN 1 ELSE 0 END as White ,
CASE e.Ethnicity_Id WHEN 3 THEN 1 ELSE 0 END as Asian,
etc
FROM T_1 TT
JOINS
WHERE
GROUP

这是意料之中的，但目标是将多个种族分配给10（多个）的种族id。我还想把它们放在一条线上。
最终结果如下：

所以我的问题有两个。如果候选人有两个以上的种族，则将记录分配到10的种族。我还需要将重复的personid分组到一行中，同时显示列的所有结果。

sql sql-server pivot tsql reporting

来源：https://stackoverflow.com/questions/62661632/trying-to-combine-multiples-of-a-key-id-into-single-row-but-with-different-valu

3条答案

按热度按时间

0aydgbwb1#

这将带来您想要的结果：

SELECT Person_ID
     , ISNULL(ID_Dummy,Ethnicity_ID) Ethnicity_ID
     , MAX(Black) Black
     , MAX(White) White
     , MAX(Asian) Asian
  FROM @T T
 OUTER APPLY(SELECT MAX(10) FROM @T T2 
               WHERE T2.Person_ID = T.Person_ID 
                 AND T2.Ethnicity_ID <> T.Ethnicity_ID
            )EthnicityOverride(ID_Dummy)
 GROUP BY Person_ID, ISNULL(ID_Dummy,Ethnicity_ID)

赞(0）回复(0）举报 2021-08-01

k0pti3hp2#

您需要条件聚合。您的查询不完整，但想法是：

select 
    person_id, 
    sum(case ethnicity_id = 1 then 1 else 0 end) as black,
    sum(case ethnicity_id = 2 then 1 else 0 end) as white,
    sum(case ethnicity_id = 3 then 1 else 0 end) as asian
from ...
where ...
group by person_id

你可能想要 max() 而不是 sum() . 我也没有得到期望结果中第二列的逻辑-也许这只是 count(*) .

赞(0）回复(0）举报 2021-08-01

ohtdti5x3#

这就是我的方法

SELECT 
    person_id,
    CASE WHEN flag = 1 THEN Ethnicity_Id ELSE 10 END AS Ethnicity_Id,
    [1] as black,
    [2] as white,
    [3] as asian
FROM  
(
    SELECT 
        person_id, 
        Ethnicity_Id as columns,
        1 as n,
        MAX(Ethnicity_Id) over(PARTITION BY person_id) as Ethnicity_Id,
        COUNT(Ethnicity_Id) over(PARTITION BY person_id) as flag
    FROM 
        #example
) AS SourceTable  
PIVOT  
(  
    MAX(n) FOR columns IN ([1], [2], [3])  
) AS PivotTable;

将id列透视成多个列，使用常量1使其与预期结果一致。
使用max（种族标识）和partition by来获取原始种族标识
使用count（种族标识）标记是否需要替换种族标识
对于10 bc，该个人id的行数超过1行
如果您需要添加更多的种族，请在中添加ID ... IN ([1], [2], [3]) ... 在选择

赞(0）回复(0）举报 2021-08-01

我来回答

sql—尝试将多个键id组合到一行中，但列中的值不同

3条答案

相关问题

热门标签

最新问答