sql server 行轉列

sqlserver把行轉成列在我們編碼中是經常遇到的我做一個小例子大家看一下

 1 --創建一個表
 2 create table PayPhoneMoney
 3 (
 4     id int identity(1,1),
 5     userName Nvarchar(20),
 6     payType nvarchar(20),
 7     money   decimal,
 8     payTime datetime,
 9     constraint pk_id primary key(id)
10 )
11 --插入點數據
12 insert into PayPhoneMoney values('小李','支付寶',20,'2012-01-03')
13 insert into PayPhoneMoney values('小陳','工行',20,'2012-01-06')
14 insert into PayPhoneMoney values('小趙','交行',50,'2012-01-06')
15 insert into PayPhoneMoney values('小陳','支付寶',60,'2012-01-06')
16 insert into PayPhoneMoney values('小趙','工行',30,'2012-01-16')
17 insert into PayPhoneMoney values('小張','中行',30,'2012-01-16')
18 insert into PayPhoneMoney values('小李','支付寶',60,'2012-01-16')

看一下表中的數據

我們要想查一下每個人所有支付形式下的總錢數如圖所示

1 -- 查一下每個人所有支付形式下的總錢數
2 select userName from PayPhoneMoney group by userName
3 select userName,
4     sum(case payType when '支付寶' then money else 0 end) as 支付寶 ,
5     sum(case payType when '工行' then money else 0 end) as 工行, 
6     sum(case payType when '交行' then money else 0 end) as 交行,
7     sum(case payType when '中行' then money else 0 end) as 中行
8 from PayPhoneMoney 
9 group by userName

--我們這只列出了幾種支付方式實際中還有很多支付方式不能一個一個都用case when 吧
--可以這樣

1 declare @cmdText varchar(8000)
2 set @cmdText='select userName, '
3 select @cmdText=@cmdText+' sum(case payType when'''+payType+'''Then money else 0 end) as '''+payType
4 +''','+char(10) from (select Distinct payType from PayPhoneMoney) T
5 print @cmdText--發現多一個逗號下面把逗號去掉
6 set @cmdText=left(@cmdText,len(@cmdText)-2)--去掉逗號
7 set @cmdText=@cmdText+'from PayPhoneMoney group by userName'
8 print @cmdText
9 exec(@cmdText)

看一下結果是一樣的吧