SQL行转列与列转行

CREATE TABLE Sell
(
  [Year] INT,
  [Quarter] NVARCHAR(10),
  Quantity INT
)
GO

INSERT INTO Sell
SELECT 2006, 'Q1', 20 UNION ALL
SELECT 2006, 'Q2', 15 UNION ALL
SELECT 2006, 'Q2', 4 UNION ALL
SELECT 2006, 'Q3', 12 UNION ALL
SELECT 2006, 'Q4', 18 UNION ALL
SELECT 2007, 'Q1', 10 UNION ALL
SELECT 2007, 'Q2', 10 UNION ALL
SELECT 2008, 'Q1', 8 UNION ALL
SELECT 2008, 'Q2', 7 UNION ALL
SELECT 2008, 'Q3', 5 UNION ALL
SELECT 2008, 'Q3', 10 UNION ALL
SELECT 2008, 'Q4', 9
GO

-- MSSQL 2005+ Pivot 静态（行转列）
SELECT *
FROM   sell PIVOT( SUM(quantity) FOR [quarter] IN ( Q1, Q2, Q3, Q4 ) ) p

-- MSSQL 2000+ 静态（行转列）
SELECT
	[Year],
	SUM(CASE [Quarter] WHEN N'Q1' THEN Quantity ELSE 0 END) Q1,
	SUM(CASE [Quarter] WHEN N'Q2' THEN Quantity ELSE 0 END) Q2,
	SUM(CASE [Quarter] WHEN N'Q3' THEN Quantity ELSE 0 END) Q3,
	SUM(CASE [Quarter] WHEN N'Q4' THEN Quantity ELSE 0 END) Q4
FROM Sell
GROUP BY [Year]

-- MSSQL 2005+ Pivot 动态（行转列）
DECLARE @sql VARCHAR(1000), @distinct_val VARCHAR(500) ;
SET @sql = 'SELECT * FROM sell PIVOT(SUM(quantity) FOR [quarter] IN (' ;
SET @distinct_val = (SELECT STUFF(( SELECT DISTINCT ',' + [quarter] FROM sell FOR XML PATH('')), 1, 1, '')) ;
SET @sql = @sql + @distinct_val + '))' + ' p'
EXEC(@sql)

-- MSSQL 2000+ 动态（行转列）
DECLARE @sql VARCHAR(2000)

SET @sql = 'SELECT [Year]'
SELECT @sql = @sql + ',SUM(CASE [Quarter] WHEN ''' + [Quarter] + ''' THEN [Quantity] ELSE 0 END) ' + [Quarter]
FROM sell
GROUP BY [Quarter]

SET @sql = @sql + ' FROM Sell GROUP BY [Year]'

EXEC(@sql)

-- 用 unpovit
select [Year],[Quarter],[Quantity]
from sell_2 unpivot(Quantity for [Quarter] in(Q1,Q2,Q3,Q4)) b

-- 不用 unpivot（静态）
select [Year],'Q1' [Quarter], Q1 [Quantity] from Sell_2 union all
select [Year],'Q2' [Quarter], Q2 [Quantity] from Sell_2 union all
select [Year],'Q3' [Quarter], Q3 [Quantity] from Sell_2 union all
select [Year],'Q4' [Quarter], Q4 [Quantity] from Sell_2
order by 1,2