SELECT ITEM1, SUM(CASE WHEN ITEM2='001' THEN QUANTITY ELSE 0 END) AS [001], SUM(CASE WHEN ITEM2='002' THEN QUANTITY ELSE 0 END) AS [002], SUM(CASE WHEN ITEM2='101' THEN QUANTITY ELSE 0 END) AS [101] FROM A GROUP BY ITEM1 如果item2是不固定的话用动态SQL
select item1,[001]=max(case item when '001' then quantity end), [002]=max(case item when '002' then quantity end), [003]=max(case item when '003' then quantity end) from tb group by item1
declare @TB1 table([item1] varchar(1),[item2] varchar(3),[quantity] int) insert @TB1 select 'A','001',200 union all select 'B','001',300 union all select 'C','002',500 union all select 'D','101',20select * FROM @TB1 PIVOT ( SUM([quantity]) FOR [item2] IN([001],[002],[101]) ) P------------------ item1 001 002 101 ----- ----------- ----------- ----------- A 200 NULL NULL B 300 NULL NULL C NULL 500 NULL D NULL NULL 20(4 row(s) affected)
---测试数据--- if object_id('[A]') is not null drop table [A] go create table [A]([item1] varchar(1),[item2] varchar(3),[quantity] int) insert [A] select 'A','001',200 union all select 'B','001',300 union all select 'C','002',500 union all select 'D','101',20
---查询--- declare @sql varchar(8000) select @sql=isnull(@sql+',','')+'sum(case when item2='''+item2+''' then quantity else 0 end) as ['+item2+']' from (select distinct item2 from a) texec ('select item1,'+@sql+' from a group by item1') ---结果--- item1 001 002 101 ----- ----------- ----------- ----------- A 200 0 0 B 300 0 0 C 0 500 0 D 0 0 20
ITEM1,
SUM(CASE WHEN ITEM2='001' THEN QUANTITY ELSE 0 END) AS [001],
SUM(CASE WHEN ITEM2='002' THEN QUANTITY ELSE 0 END) AS [002],
SUM(CASE WHEN ITEM2='101' THEN QUANTITY ELSE 0 END) AS [101]
FROM
A
GROUP BY ITEM1
如果item2是不固定的话用动态SQL
[002]=max(case item when '002' then quantity end),
[003]=max(case item when '003' then quantity end)
from tb
group by item1
declare @TB1 table([item1] varchar(1),[item2] varchar(3),[quantity] int)
insert @TB1
select 'A','001',200 union all
select 'B','001',300 union all
select 'C','002',500 union all
select 'D','101',20select *
FROM @TB1
PIVOT
(
SUM([quantity])
FOR [item2]
IN([001],[002],[101])
) P------------------
item1 001 002 101
----- ----------- ----------- -----------
A 200 NULL NULL
B 300 NULL NULL
C NULL 500 NULL
D NULL NULL 20(4 row(s) affected)
if object_id('[A]') is not null drop table [A]
go
create table [A]([item1] varchar(1),[item2] varchar(3),[quantity] int)
insert [A]
select 'A','001',200 union all
select 'B','001',300 union all
select 'C','002',500 union all
select 'D','101',20
---查询---
declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')+'sum(case when item2='''+item2+''' then quantity else 0 end) as ['+item2+']'
from
(select distinct item2 from a) texec ('select item1,'+@sql+' from a group by item1')
---结果---
item1 001 002 101
----- ----------- ----------- -----------
A 200 0 0
B 300 0 0
C 0 500 0
D 0 0 20