SQL 行转列

 原始

2008  saa
2008  2008年干1
2008  aaaa 
2008  2008好干3 
2008  20好好干1 
2008  2干5 

 

 结果

2008    2008好干3     2008年干1   20好好干1     2干5   aaaa   saa 

--行变列
DECLARE @SQL VARCHAR(8000)
SET @SQL=''
SELECT @SQL=@SQL+','+'MAX(CASE WHEN Content='''+RTRIM(Content)+''' THEN RTRIM(Content) ELSE '''' END) AS ['+RTRIM(Content)+']'
FROM (SELECT DISTINCT  Content FROM Answers) CurrYear

SET @SQL='SELECT CurrYear '+@SQL+' FROM Answers GROUP BY CurrYear'

EXEC(@SQL)


--合并为一列，中间用逗号隔开
DECLARE @SQL VARCHAR(8000)
SET @SQL=''
SELECT @SQL=@SQL+'+'',''+'+'MAX(CASE WHEN Content='''+RTRIM(Content)+''' THEN RTRIM(Content) ELSE '''' END)'
FROM (SELECT DISTINCT  Content FROM Answers) CurrYear

SET @SQL='SELECT cast(CurrYear as varchar(10))'+@SQL+' FROM Answers GROUP BY CurrYear'

EXEC(@SQL)