--准备环境与测试数据
USE test
GO
IF OBJECT_ID('t1') IS NOT NULL
    DROP TABLE t1
CREATE TABLE t1
(
    person    varchar(5)
    ,item    varchar(5)
    ,qty    int
)
INSERT INTO t1
VALUES
    ('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 t1
GROUP 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 t2
SELECT @query += 'FROM t1 GROUP BY person'
EXEC ( @query )

------解决方案--------------------
SQL code
IF OBJECT_ID('t1') IS NOT NULL
    DROP TABLE t1
CREATE TABLE t1
(
    person    varchar(5)
    ,item    varchar(5)
    ,qty    int
)
INSERT INTO t1
VALUES
    ('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,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          0
B      17          23          14          0
C      0           0           19          22

(3 row(s) affected)