关于SQL列行转换或则添加行

[老潇]

表A:
Code Month Price BasePrice
A001 1月 10 5
A001 2月 11 6
A002 3月 12 7
A002 4月 13 8


需要结果
Code Month Price BasePrice
A001 1月 10 5
A001 2月 11 6
A001 3月 0 0
A001 4月 0 0
. . . .
. . . .
. . . .
A001 12月 0 0
A002 1月 0 0
A002 2月 0 0
A002 3月 12 7
A002 4月 13 8
A002 5月 0 0
. . . .
. . . .
. . . .
A002 12月 0 0

[老潇]

也就是说要将A001,A002的Month列都变成12行(12个月),后面的Price和BasePrice为空就取0

[chuifengde]

select CC.Code,CC.Month,isnull(Price,0) Price,isnull(BasePrice,0) BasePrice
from
(select Code,Month from (select distinct Code from A)AA,(select '1月' Month union all select '2月'....)BB)CC
Left Join A On CC.Code=A.Code and CC.Month=A.Month

[nzperfect]

--> 测试数据: [A]
if object_id('[A]') is not null drop table [A]
create table [A] (Code varchar(4),Month varchar(3),Price int,BasePrice int)
insert into [A]
select 'A001','1月',10,5 union all
select 'A001','2月',11,6 union all
select 'A002','3月',12,7 union all
select 'A002','4月',13,8


declare @t table(mth varchar(20))
insert into @t select '1月' union all
select '2月' union all select '3月' union all
select '4月' union all select '5月' union all
select '6月' union all select '7月' union all
select '8月' union all select '9月' union all
select '10月' union all select '11月' union all
select '12月'
select b.code,b.mth,isnull(a.Price,0) as Price,isnull(a.BasePrice,0) as BasePrice from(
select distinct a.code,b.mth from (select distinct code,[month] from A) as a  cross join @t as b
) as b
left join [A] as a on a.code=b.code and b.mth=a.month order by b.code,cast(replace(b.mth,'月','')as int)

[utpcb]

select CC.Code,CC.Month,isnull(Price,0) Price,isnull(BasePrice,0) BasePrice
from
(select Code,Month from (select distinct Code from A)AA,(select '1月' Month union all select '2月'....)BB)CC
Left Join A On CC.Code=A.Code and CC.Month=A.Month

[nzperfect]

忘记贴结果:

/*
A001	1月	10	5
A001	2月	11	6
A001	3月	0	0
A001	4月	0	0
A001	5月	0	0
A001	6月	0	0
A001	7月	0	0
A001	8月	0	0
A001	9月	0	0
A001	10月	0	0
A001	11月	0	0
A001	12月	0	0
A002	1月	0	0
A002	2月	0	0
A002	3月	12	7
A002	4月	13	8
A002	5月	0	0
A002	6月	0	0
A002	7月	0	0
A002	8月	0	0
A002	9月	0	0
A002	10月	0	0
A002	11月	0	0
A002	12月	0	0
*/

[老潇]

谢谢楼上各位啦
呵呵