如何在SQL查询中将行转换为列

Question

我有这个输出：

Contact_Type    Category_Type        Category_Count
---------------------------------------------------
Window          Admissions              1775
Window          Financial Aid          17377
Window          Miscellaneous           2720
Window          Student Financials     14039
Phone           Admissions              5758
Phone           Financial Aid          10048
Phone           Miscellaneous           4497
Phone           Registration              11
Phone           Student Financials      4857

这是我的查询语句：

SELECT 
    Contact_Type, Category_Type1, Category_Type2, Category_Type3,
    Category_Type4, Category_Type5 
FROM
    (SELECT 
         CASE
            WHEN event.contact_type = 0 THEN 'Window' 
            WHEN event.contact_type = 1 THEN 'Phone' 
         END AS Contact_Type,
         cat.category_type AS Category_Type,
         COUNT(ec.category_id) AS Category_Count,
         'Category_Type' + CAST(ROW_NUMBER() OVER (PARTITION BY Contact_Type
ORDER BY Contact_Type) AS varchar(20)) AS ColumnSequence
     FROM 
         yLines.ylines_event AS Event
     JOIN 
         ylines.ylines_event_category AS ec ON ec.event_id = event.event_id
     JOIN 
         ylines.ylines_category AS cat ON ec.category_id = cat.category_id
     WHERE /*event.contact_type = '0' AND*/    
         CAST(FORMAT(event.event_date_time, 'yyyy') AS int) BETWEEN 2014 AND dateadd(year, 1, event.event_date_time)
     GROUP BY 
         Category_Type, Contact_Type) a 
PIVOT
    (MAX(Contact_Type)
     FOR ColumnSequence IN (Category_Type1, Category_Type2, Category_Type3,
Category_Type4, Category_Type5)) as piv;   

如果我运行此代码会出现错误：

Msg 207, Level 16, State 1, Line 1
列名 'Contact_Type' 无效

我似乎无法解决这个问题。我正在尝试转置它，以便我只看到两行，分别为“Windows”和“Phone”，并将五个类别类型转置为五列，并在每个列中显示计数。我正在编写 T-SQL 语句。请帮忙！

- Flemming Hald

---

你有没有调查过使用 PIVOT？ - corky_bantam

Answer 1

在 group by 子句中，你要说

`group by Category_Type, Contact_Type`

然而，您已经定义了一个名为contact_type的计算列，但在group by子句中并不存在。您应该使用

GROUP BY Category_Type, -- Contact_Type
case 
when event.contact_type=0 then 'Window' 
when event.contact_type=1 then 'Phone' 
end 

在编程中，给计算列命名时最好不要与任何表中的列重名。

- Vamsi Prabhala

---

一个case语句不是计算列。计算列包括SUM、AVG、MAX等。如果将“Contact_Type”从group by中移除，会导致额外的错误。 - Flemming Hald

那么你会用什么来代替计算列呢？ - Vamsi Prabhala

Answer 2

使用案例语句进行数据透视：

SELECT CONTACT_TYPE,
SUM(CASE WHEN CATEGORY_TYPE='Admissions' THEN CATEGORY_COUNT END) ADMISSIONS,
SUM(CASE WHEN CATEGORY_TYPE='Financial Aid' THEN CATEGORY_COUNT END) FINANCIAL_AID,
SUM(CASE WHEN CATEGORY_TYPE='Miscellaneous' THEN CATEGORY_COUNT END) MISCELLANEOUS,
SUM(CASE WHEN CATEGORY_TYPE='Student Financials' THEN CATEGORY_COUNT END) STUDENT_FINANCIALS,
SUM(CASE WHEN CATEGORY_TYPE='Registration' THEN CATEGORY_COUNT END) REGISTRATION
FROM TEST_3 GROUP BY CONTACT_TYPE;

输出：

CONTACT_TYPE    ADMISSIONS  FINANCIAL_AID   MISCELLANEOUS   STUDENT_FINANCIALS  REGISTRATION
 Phone             5758         10048           4497               4857                11
 Window            1775         17377           2720              14039              null

- debug_04

---

这个不是修复的方法。问题出在第一行的'Contact_Type'字段。不过我同意用case语句进行旋转处理更好。 - Flemming Hald

Answer 3

我会尝试在动态环境下完成它

       ; WITH [CONTACT]
     AS (
         SELECT *
           FROM (
                 VALUES 
                        ('Window', 'Admissions        ', ' 1775')
                      , ('Window', 'Financial Aid     ', '17377')
                      , ('Window', 'Miscellaneous     ', ' 2720')
                      , ('Window', 'Student Financials', '14039')
                      , ('Phone ', 'Admissions        ', ' 5758')
                      , ('Phone ', 'Financial Aid     ', '10048')
                      , ('Phone ', 'Miscellaneous     ', ' 4497')
                      , ('Phone ', 'Registration      ', '   11')
                      , ('Phone ', 'Student Financials', ' 4857')
                ) X ([Contact_Type], [Category_Type], [Category_Count])
     )   


     SELECT * 
       INTO #TEMP_PIVOT
       FROM [CONTACT]

    DECLARE @TYPE    VARCHAR(MAX)   
        SET @TYPE = STUFF( 
                          (SELECT DISTINCT ', ' + QUOTENAME(RTRIM(LTRIM([CATEGORY_TYPE])))
                                    FROM #TEMP_PIVOT
                                     FOR XML PATH('')
                          )
                          , 1, 1, '')

   DECLARE @SQL    VARCHAR(MAX)
       SET @SQL = '  SELECT [CONTACT_TYPE] '
                + '       , ' + @TYPE
                + '    FROM #TEMP_PIVOT '
                + '   PIVOT ( '
                + '           MAX([CATEGORY_COUNT]) '
                + '           FOR [CATEGORY_TYPE] IN (' + @TYPE + ')'
                + '         ) P '

     EXECUTE (@SQL)

- n00bs