Der PIVOT-Operator bietet diese Funktionalität nur für eine Spalte. Wenn man mehrere PIVOT-Ergebnisse in einem Resultset haben möchte, kann man dies durch einen Join der beiden PIVOTs erreichen.

Set Nocount on

go
CREATE TABLE #p
( Year      SMALLINT,
  Quarter   TINYINT,
  Amount      DECIMAL(2,1) )
GO
INSERT INTO #p VALUES (1990, 1, 1.1)
INSERT INTO #p VALUES (1990, 2, 1.2)
INSERT INTO #p VALUES (1990, 3, 1.3)
INSERT INTO #p VALUES (1990, 4, 1.4)
INSERT INTO #p VALUES (1991, 1, 2.1)
INSERT INTO #p VALUES (1991, 2, 2.2)
INSERT INTO #p VALUES (1991, 3, 2.3)
INSERT INTO #p VALUES (1991, 4, 2.4)
GO

-- Ohne schöne Spaltenüberschrift
select a.year, a.[1], a.[2], a.[3], a.[4], b.[1], b.[2], b.[3], b.[4]
from
(SELECT * FROM #p
PIVOT (sum(Amount) FOR Quarter IN ( [1],[2],[3],[4] )) as x) a,
(SELECT * FROM #p
PIVOT (count(Amount) FOR Quarter IN ( [1],[2],[3],[4] )) as y) b
where a.year = b.year
GO

-- Mit schöner Spaltenüberschrift
select a.year, a.Q1, a.Q2, a.Q3, a.Q4, b.Count_Q1, b.Count_Q2,
b.Count_Q3, b.Count_Q4
from
(SELECT year, [1] AS Q1,[2] AS Q2,[3] AS Q3, [4] AS Q4
FROM
(SELECT * FROM #p) AS #p
PIVOT (sum(#p.Amount) FOR #p.Quarter IN ( [1],[2],[3],[4] )
) AS x) a,
(SELECT year, [1] AS Count_Q1,[2] AS Count_Q2,[3] AS Count_Q3, [4] AS
Count_Q4
FROM
(SELECT * FROM #p) AS #p
PIVOT (count(#p.Amount) FOR #p.Quarter IN ( [1],[2],[3],[4] )
) AS x) b
where a.year = b.year


go
drop Table #p

  Pivot SQL