我有这个输出:
如果我运行此代码会出现错误:
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;
如果我运行此代码会出现错误:
我似乎无法解决这个问题。我正在尝试转置它,以便我只看到两行,分别为“Windows”和“Phone”,并将五个类别类型转置为五列,并在每个列中显示计数。我正在编写 T-SQL 语句。请帮忙!Msg 207, Level 16, State 1, Line 1
列名 'Contact_Type' 无效
PIVOT
? - corky_bantam