SQL查询需要合并相似的行

Question

我有一个如下的 sql 查询：

    SELECT
        EVENTTYPEDESCRIPTION,
        YEAR(EVENTSTARTDATE) as 'EVENTYEAR',
        COUNT(PARTICIPANTID) AS 'TOTALPARTICIPANTS'
    FROM WEBPROGRAMPARTICIPANTS
    INNER JOIN WEBPROGRAMS
        ON WEBPROGRAMPARTICIPANTS.PROGRAMCODE = WEBPROGRAMS.PROGRAMCODE
    INNER JOIN WEBEVENTS
        ON WEBPROGRAMS.PROGRAMID = WEBEVENTS.EVENTID
    INNER JOIN WEBEVENTTYPEDESCRIPTIONS
        ON WEBEVENTS.EVENTTYPE = WEBEVENTTYPEDESCRIPTIONS.EVENTTYPE
    WHERE WEBPROGRAMPARTICIPANTS.ACCEPTED = 1
    GROUP BY EVENTTYPEDESCRIPTION, YEAR(EVENTSTARTDATE)
    ORDER BY EVENTTYPEDESCRIPTION, YEAR(EVENTSTARTDATE)

返回以下结果：

EventTypeDescripti  Year    TotalParticipants
Affiliate Workshop  2004    96
Affiliate Workshop  2005    132
Affiliate Workshop  2006    94
Affiliate Workshop  2007    125
Affiliate Workshop  2008    121
Affiliate Workshop  2010    170
Affiliate Workshop  2011    1
IPAM Long Program   2000    59
IPAM Long Program   2001    203
IPAM Long Program   2002    94
IPAM Long Program   2003    182
IPAM Long Program   2004    147
IPAM Long Program   2005    123
IPAM Long Program   2006    99
IPAM Long Program   2007    116
IPAM Long Program   2008    98
IPAM Long Program   2009    127
IPAM Long Program   2010    147
IPAM Long Program   2011    105
IPAM Long Program   2012    14
IPAM Reunion Conference 2002    108
IPAM Reunion Conference 2003    100
IPAM Reunion Conference 2004    98
IPAM Reunion Conference 2005    68

我的问题是如何重建查询以将每个 EventTypeDescription 压缩为一行，并将每年显示为一列及其各自的计数

示例：

Description            Year            Year           etc...
Affiliate Workshop     96              132            ....    

Answer 1

好的，因为我不知道结果的每一列来自哪个表，所以我必须为此使用临时表。然后，您将需要动态 sql，因此请先查看此链接。然后，尝试这样的事情：

DECLARE @Years NVARCHAR(MAX), @Query NVARCHAR(MAX)
SET @Years = ''

SELECT eventtypedescription, 
       YEAR(eventstartdate) AS 'EVENTYEAR', 
       COUNT(participantid) AS 'TOTALPARTICIPANTS' 
INTO #Results
FROM   webprogramparticipants 
INNER JOIN webprograms 
 ON webprogramparticipants.programcode = webprograms.programcode 
INNER JOIN webevents 
 ON webprograms.programid = webevents.eventid 
INNER JOIN webeventtypedescriptions 
 ON webevents.eventtype = webeventtypedescriptions.eventtype 
WHERE  webprogramparticipants.accepted = 1 
GROUP  BY eventtypedescription, YEAR(eventstartdate) 


SELECT @Years = @Years + '[Year ' + CAST(EVENTYEAR AS VARCHAR) +'],'
FROM #Results
GROUP BY EVENTYEAR
ORDER BY EVENTYEAR

SET @Years = LEFT(@Years,LEN(@Years)-1)

SET @Query = '
SELECT *
FROM (  SELECT eventtypedescription, TOTALPARTICIPANTS, ''Year ''+CAST(EVENTYEAR AS VARCHAR) Years
        FROM #Results) T
PIVOT(SUM(TOTALPARTICIPANTS) FOR Years IN ('+@Years+')) AS PT'

EXEC sp_executesql @Query

Answer 2

1. 使用游标生成您需要的行的字符串
2. 使用数据透视表数据透视表 EventTypeDescription 列上的数据。示例正在路上。

Answer 3

感谢大家的努力。一旦我们的数据库升级到 2008，我就会记住 PIVOT 示例。由于现在我们使用 SQL Server 2000，所以我决定在我的 C# 代码中进行数据透视。

无论如何，这种方式实际上对我来说更好，因为我可以传入任何选择查询结果，并且它将对其进行旋转。