这种不带求和的转置怎么写

[ah_2056]

第一次遇到这种转置，貌似有点复杂。

字段1 字段2 字段3
a A1 2014
a A2 2015
b dd 2014
b ff 2015
c A3 2015
c A4 2014
d a1 2015
d a2 2014



字段1 2014 2015
a a1 a2
b dd ff
c a4 a3
d a2 a1

[还在加载中灬]

行转列又不一定要SUM，只要聚合即可

DECLARE @SQL VARCHAR(8000)--SQL2005+可以用VARCHAR(MAX)代替
SET @SQL='SELECT 字段1'
SELECT @SQL=@SQL+',MAX(CASE WHEN 字段3='''+CAST(字段3 AS VARCHAR)+''' THEN 字段2 END)['+CAST(字段3 AS VARCHAR)+']'
FROM TB GROUP BY 字段3
SET @SQL=@SQL+'FROM TB GROUP BY 字段1'
PRINT @SQL
EXEC(@SQL)
--静态
SELECT 字段1
    ,MAX(CASE WHEN 字段3='2014' THEN 字段2 END)[2014]
    ,MAX(CASE WHEN 字段3='2015' THEN 字段2 END)[2015]
FROM TB
GROUP BY 字段1

[还在加载中灬]

--动态
DECLARE @SQL VARCHAR(8000)--SQL2005+可以用VARCHAR(MAX)代替
SET @SQL='SELECT 字段1'
SELECT @SQL=@SQL+',MAX(CASE WHEN 字段3='''+CAST(字段3 AS VARCHAR)+''' THEN 字段2 END)['+CAST(字段3 AS VARCHAR)+']'
FROM TB GROUP BY 字段3
SET @SQL=@SQL+'FROM TB GROUP BY 字段1'
PRINT @SQL
EXEC(@SQL)
--静态
SELECT 字段1
    ,MAX(CASE WHEN 字段3='2014' THEN 字段2 END)[2014]
    ,MAX(CASE WHEN 字段3='2015' THEN 字段2 END)[2015]
FROM TB
GROUP BY 字段1

[Tiger_Zhao]

SELECT *
  FROM table1
 PIVOT (
        MAX(字段2)
        FOR 字段3 IN ([2014], [2015])
       ) p

字段1 2014 2015
----- ---- ----
a     A1   A2
b     dd   ff
c     A4   A3
d     a2   a1