高分SQL中怎么实现如上功能的查询

高分求助：SQL中如何实现如下功能的查询
假如有这样一张表

personitemqty
Ab15
Ac18
Ad21
Bb17
Bc23
Bd14
C d19
C e22
该如何用SQL语句实现下面形式的展现？

bcde
A 1518210
B1723140
C001922


[解决办法]
如果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)