时间戳

首页 > 解决方案 > SQL GROUP BY 列值

sql - SQL GROUP BY 列值

问题描述

选择以下数据时，我想制作它，以便我可以按列分组，以便所有“Y”。下图显示了当前的外观；

在此处输入图像描述

在此处输入图像描述

第二张图片显示了最终结果的外观。

SELECT p.FirstName
    ,p.Surname
    ,CASE
        WHEN a.ActivityName = 'Jumping' THEN 'Y'
        ELSE 'N'
    END AS 'Jumping'
    ,CASE
        WHEN a.ActivityName = 'Dancing' THEN 'Y'
        ELSE 'N'
    END AS 'Dancing'
    ,CASE
        WHEN a.ActivityName = 'Walking' THEN 'Y'
        ELSE 'N'
    END AS 'Walking'
FROM #TempActivity a 
    INNER JOIN #TempPerson p ON a.AID = p.ActivityID
WHERE FirstName = 'John'
    AND Surname = 'Smith'

下面我复制了整个建表过程，让大家可以看到所有的数据和表结构。我发布了在这种情况下设计表格的更好方法，但这只是一个示例，以便我知道在其他地方应用的方法；

CREATE TABLE #TempActivity(
AID INT
,ActivityName VARCHAR(100)
)

CREATE TABLE #TempPerson(
PID INT
,FirstName VARCHAR(100)
,Surname VARCHAR(100)
,ActivityID INT
)

INSERT INTO #TempActivity
VALUES (1,'Jumping')
    ,(2,'Dancing')
    ,(3,'Walking')

INSERT INTO #TempPerson
VALUES (1,'John', 'Smith', 1)
    ,(2,'John', 'Smith', 2)
    ,(3,'John', 'Smith', 3)
    ,(4,'Jane','Smith', 1)
    ,(5,'Jane','Smith', 2)

SELECT * FROM #TempActivity
SELECT * FROM #TempPerson

SELECT p.FirstName
    ,p.Surname
    ,a.ActivityName
  FROM #TempActivity a 
INNER JOIN #TempPerson p ON a.AID = p.ActivityID
  WHERE FirstName = 'John'
AND Surname = 'Smith'

SELECT p.FirstName
    ,p.Surname
    ,CASE
        WHEN a.ActivityName = 'Jumping' THEN 'Y'
        ELSE 'N'
    END AS 'Jumping'
    ,CASE
        WHEN a.ActivityName = 'Dancing' THEN 'Y'
        ELSE 'N'
    END AS 'Dancing'
    ,CASE
        WHEN a.ActivityName = 'Walking' THEN 'Y'
        ELSE 'N'
    END AS 'Walking'
    FROM #TempActivity a 
INNER JOIN #TempPerson p ON a.AID = p.ActivityID
WHERE FirstName = 'John'
AND Surname = 'Smith'

DROP TABLE #TempActivity;
DROP TABLE #TempPerson;

标签： sqlgroup-byssms

解决方案

SELECT p.FirstName
    ,p.Surname
    ,CASE WHEN SUM(CASE
        WHEN a.ActivityName = 'Jumping' THEN 1
        ELSE 0 END) > 0 THEN 'Y' 
        ELSE 'N' 
    END AS 'Jumping'
    ,CASE WHEN SUM(CASE
        WHEN a.ActivityName = 'Dancing' THEN 1
        ELSE 0 END) > 0 THEN 'Y' 
        ELSE 'N'
    END AS 'Dancing'
    ,CASE WHEN SUM(CASE
        WHEN a.ActivityName = 'Walking' THEN 1
        ELSE 0 END) > 0 THEN 'Y' 
        ELSE 'N'
    END AS 'Walking'
FROM #TempActivity a 
    INNER JOIN #TempPerson p ON a.AID = p.ActivityID
WHERE FirstName = 'John'
    AND Surname = 'Smith'
GROUP BY FirstName, SurName

推荐阅读