日期 发货仓库 销售金额
2006-04 A 100
2006-04 B 200
2006-04 C 300
2006-04 D 400
2006-05 A 500
2006-05 B 600
2006-05 C 700
2006-05 D 800 将以上记录转化成下列格式
日期 A B C D
2006-04 100 200 300 400
2006-05 500 600 700 800
2006-04 A 100
2006-04 B 200
2006-04 C 300
2006-04 D 400
2006-05 A 500
2006-05 B 600
2006-05 C 700
2006-05 D 800 将以上记录转化成下列格式
日期 A B C D
2006-04 100 200 300 400
2006-05 500 600 700 800
SELECT '2006-04', 'A', 100
UNION
SELECT '2006-04', 'B', 200
UNION
SELECT '2006-04', 'C', 300
UNION
SELECT '2006-04', 'D', 400
UNION
SELECT '2006-05', 'A', 500
UNION
SELECT '2006-05', 'B', 600
UNION
SELECT '2006-05', 'C', 700
UNION
SELECT '2006-05', 'D', 800
SELECT 日期,
(SELECT SUM(销售金额) FROM @SOURCE WHERE 日期 = A.日期 AND 发货仓库 = 'A') AS 发货仓库A,
(SELECT SUM(销售金额) FROM @SOURCE WHERE 日期 = A.日期 AND 发货仓库 = 'B') AS 发货仓库B,
(SELECT SUM(销售金额) FROM @SOURCE WHERE 日期 = A.日期 AND 发货仓库 = 'C') AS 发货仓库C,
(SELECT SUM(销售金额) FROM @SOURCE WHERE 日期 = A.日期 AND 发货仓库 = 'D') AS 发货仓库D
FROM @SOURCE AS A GROUP BY 日期/*
结果:
日期 A B C D
2006-04 100 200 300 400
2006-05 500 600 700 800
*/
日期 datetime,
发货仓库 varchar(10),
销售金额 int
)insert #a select '2006-04-01','A',100
UNION ALL SELECT '2006-04-01','B',200
UNION ALL SELECT '2006-04-01','C',300
UNION ALL SELECT '2006-04-01','D',400
UNION ALL SELECT '2006-05-01','A',500
UNION ALL SELECT '2006-05-01','B',600
UNION ALL SELECT '2006-05-01','C',700
UNION ALL SELECT '2006-05-01','D',800
SELECT 日期,
SUM(CASE 发货仓库 WHEN 'A' THEN 销售金额 ELSE 0 END) AS 'A',
SUM(CASE 发货仓库 WHEN 'B' THEN 销售金额 ELSE 0 END) AS 'B',
SUM(CASE 发货仓库 WHEN 'C' THEN 销售金额 ELSE 0 END) AS 'C',
SUM(CASE 发货仓库 WHEN 'D' THEN 销售金额 ELSE 0 END) AS 'D'
FROM #A
GROUP BY 日期
日期 datetime,
发货仓库 varchar(10),
销售金额 int
)insert stock select '2006-04-01','A',100
UNION ALL SELECT '2006-04-01','B',200
UNION ALL SELECT '2006-04-01','C',300
UNION ALL SELECT '2006-04-01','D',400
UNION ALL SELECT '2006-05-01','A',500
UNION ALL SELECT '2006-05-01','B',600
UNION ALL SELECT '2006-05-01','C',700
UNION ALL SELECT '2006-05-01','D',800
godeclear @t varchar(8000)
select @t=''
select @t=@t + ', sum(case when 发货仓库=''' +a.发货仓库+ ''' then 销售金额 else 0 end) ' + a.发货仓库
from (select distinct 发货仓库 from stock) a
select @t='日期 ' + @t + 'from stock group by 日期'
exec(@t)drop table stock
日期 datetime,
发货仓库 varchar(10),
销售金额 int
)insert stock select '2006-04-01','A',100
UNION ALL SELECT '2006-04-01','B',200
UNION ALL SELECT '2006-04-01','C',300
UNION ALL SELECT '2006-04-01','D',400
UNION ALL SELECT '2006-05-01','A',500
UNION ALL SELECT '2006-05-01','B',600
UNION ALL SELECT '2006-05-01','C',700
UNION ALL SELECT '2006-05-01','D',800
godeclear @t varchar(8000)
select @t=''
select @t=@t + ', sum(case when 发货仓库=''' +a.发货仓库+ ''' then 销售金额 else 0 end) ' + a.发货仓库
from (select distinct 发货仓库 from stock) a
select @t='日期 ' + @t + 'from stock group by 日期'
exec(@t)drop table stock
日期 datetime,
发货仓库 varchar(10),
销售金额 int
)insert stock select '2006-04-01','A',100
UNION ALL SELECT '2006-04-01','B',200
UNION ALL SELECT '2006-04-01','C',300
UNION ALL SELECT '2006-04-01','D',400
UNION ALL SELECT '2006-05-01','A',500
UNION ALL SELECT '2006-05-01','B',600
UNION ALL SELECT '2006-05-01','C',700
UNION ALL SELECT '2006-05-01','D',800declare @t varchar(8000)
select @t=''
select @t=@t + ', sum(case when 发货仓库=''' +a.发货仓库+ ''' then 销售金额 else 0 end) ' + a.发货仓库
from (select distinct 发货仓库 from stock) a
select @t='select 日期 ' + @t + ' from stock group by 日期'
exec(@t)drop table stock
isnull(C.销售金额,0) as C, isnull(D.销售金额,0) as D
from table_name P
left join table_name A on A.发货仓库='A' and P.日期=A.日期
left join table_name B on B.发货仓库='B' and P.日期=B.日期
left join table_name C on C.发货仓库='C' and P.日期=C.日期
left join table_name D on D.发货仓库='D' and P.日期=D.日期
SELECT * FROM @SOURCE
PIVOT
(MAX(销售金额)
FOR 发货仓库 IN(A,B,C,D)
)AS PIT
create table #temp1 (日期 char(25),发货仓库 char(1),销售金额 int)insert into #temp1 select '2006-04','A',100 union all
select '2006-04','B',200 union all
select '2006-04','C',300 union all
select '2006-04','D',400 union all
select '2006-05','A',500 union all
select '2006-05','B',600 union all
select '2006-05','C',700 union all
select '2006-05','D',800
declare @str varchar(8000)
set @str='select 日期'
select @str=@str+',['+rtrim(发货仓库)+']=sum(case 发货仓库 when '''+rtrim(发货仓库)+''' then 销售金额 else 0 end)'
from #temp1
group by 发货仓库exec (@str+'from #temp1 group by 日期')
/*
日期 A B C D
2006-04 100 200 300 400
2006-05 500 600 700 800
*/