假如有这样一张表
person item qty
A b 15
A c 18
A d 21
B b 17
B c 23
B d 14
C d 19
C e 22
该如何用SQL语句实现下面形式的展现？
b c d e
A 15 18 21 0
B 17 23 14 0
C 0 0 19 22
------解决方案--------------------
如果item出现的内容不固定，需要用其他方法。
- SQL code
--准备环境与测试数据USE testGOIF OBJECT_ID('t1') IS NOT NULL DROP TABLE t1CREATE TABLE t1( person varchar(5) ,item varchar(5) ,qty int)INSERT INTO t1VALUES ('A','b',15) ,('A','c',18) ,('A','d',21) ,('B','b',17) ,('B','c',23) ,('B','d',14) ,('C','d',19) ,('C','e',22)GO--实现所需功能的代码SELECT person ,b=MAX(case item when 'b' then qty else 0 end) ,c=MAX(case item when 'c' then qty else 0 end) ,d=MAX(case item when 'd' then qty else 0 end) ,e=MAX(case item when 'e' then qty else 0 end)FROM t1GROUP BY person
------解决方案--------------------
这个代码也能满足要求，还能适应item字段内容不固定的情况。
- SQL code
DECLARE @query VARCHAR(1000);SELECT @query = 'SELECT person';SELECT @query += ',' + t2.item + '=MAX(case item when ''' + t2.item + ''' then qty else 0 end)'FROM ( SELECT DISTINCT item FROM t1 ) AS t2SELECT @query += 'FROM t1 GROUP BY person'EXEC ( @query )
------解决方案--------------------
- SQL code
IF OBJECT_ID('t1') IS NOT NULL DROP TABLE t1CREATE TABLE t1( person varchar(5) ,item varchar(5) ,qty int)INSERT INTO t1VALUES ('A','b',15) ,('A','c',18) ,('A','d',21) ,('B','b',17) ,('B','c',23) ,('B','d',14) ,('C','d',19) ,('C','e',22)GOselect person,ISNULL(b,0) AS b,ISNULL(c,0) AS C,ISNULL(d,0) AS D,ISNULL(e,0) AS E--intoCusOrders from ( select person,item,qty from t1 ) sourceOrder pivot ( sum(qty) for item in(b,c,d,e) ) as targetOrder person b C D E------ ----------- ----------- ----------- -----------A 15 18 21 0B 17 23 14 0C 0 0 19 22(3 row(s) affected)