我有两张桌子
学生：
Name Status1 Syear SCode
--------------------
kk A 2000 1
ra A 2001 2
Paras L 2000 2
Prit L 2001 2
Poot A 2002 4
程序：
PName PCode
--------------------
Msc 1
DC 2
PO 4
加入ID
所需输出：
SELECT *
FROM
(SELECT
Program.PName AS v, Status1
FROM
Student, Program
WHERE
Student.PCode = SCode
GROUP BY
Program.PName, Student.Syear, Status1) AS src
pivot
(
count(v)
FOR Status1 IN ([A],)
) as piv
它不显示PNAME在输出中
A L
-----------
1 0
1 2
1 0
所需的输出
PNAME A L
-----------
Msc 1 0
DC 1 2
PO 1 0
1.静态PIVOT
如果事先知道列名，则可以执行此操作
SELECT PName,ISNULL([A],0) [A],ISNULL([L],0)[L] FROM
(
-- Source data for pivoting
SELECT P.PName,Status1,
COUNT(Status1)OVER(PARTITION BY PNAME,Status1)CNT
FROM #PROGRAM P
JOIN #Student S ON P.PCODE=S.SCODE
) x
PIVOT
(
--Defines the values in each dynamic columns
MIN(CNT)
-- Get the names of columns to pivot
FOR Status1 IN ([A],[L])
) p
ORDER BY PName
2.动态皮沃特
如果事先不知道列数，则可以进行动态数据透视。
首先动态获取列以进行旋转
DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + Status1 + ']', '[' + Status1 + ']')
FROM (SELECT DISTINCT Status1 FROM #Student) PV
ORDER BY Status1
现在，下面的变量用于替换NULL零。
DECLARE @NulltoZeroCols NVARCHAR (MAX)
SELECT @NullToZeroCols = SUBSTRING((SELECT ',ISNULL(['+Status1+'],0) AS ['+Status1+']'
FROM (SELECT DISTINCT Status1 FROM #Student)TAB
ORDER BY Status1 FOR XML PATH('')),2,8000)
现在旋转它。我已经在里面写了逻辑
DECLARE @query NVARCHAR(MAX)
SET @query = '-- This outer query forms your pivoted result
SELECT PName,'+@NullToZeroCols+' FROM
(
-- Source data for pivoting
SELECT P.PName,Status1,
COUNT(Status1)OVER(PARTITION BY PNAME,Status1)CNT
FROM #PROGRAM P
JOIN #Student S ON P.PCODE=S.SCODE
) x
PIVOT
(
--Defines the values in each dynamic columns
MIN(CNT)
-- Get the names from the @cols variable to show as column
FOR Status1 IN (' + @cols + ')
) p
ORDER BY PName;'
EXEC SP_EXECUTESQL @query
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句