高分求行专列。。。。100分
id BatchID BOMItem,BeginDate EndDate,BeginningStock EndingStock VGUID
-------------------- ------------------------ ----------------------------------------------------- --------
1,'20131027', '3605051925592', '2013-10-27','2013-11-02',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
2,'20131103', '3605051925592', '2013-11-03','2013-11-09',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
3,'20131110', '3605051925592', '2013-11-10','2013-11-16',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
4,'20131117', '3605051925592', '2013-11-17','2013-11-23',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
5,'20131124', '3605051925592', '2013-11-24','2013-11-30',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
6,'20131201', '3605051925592', '2013-12-01','2013-12-07',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
7,'20131208', '3605051925592', '2013-12-08','2013-12-14',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
8,'20131215', '3605051925592', '2013-12-15','2013-12-21',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
9,'20131222', '3605051925592', '2013-12-22','2013-12-28',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
10,'20131229', '3605051925592', '2013-12-29','2014-01-04',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
11,'20140105', '3605051925592', '2014-01-05','2014-01-11',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
12,'20140112', '3605051925592', '2014-01-12','2014-01-18',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
13,'20140119', '3605051925592', '2014-01-19','2014-01-25',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
14,'20140126', '3605051925592', '2014-01-26','2014-02-01',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
15,'20140202', '3605051925592', '2014-02-02','2014-02-08',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
16,'20140209', '3605051925592', '2014-02-09','2014-02-15',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
1,'20131027', '3605051772882', '2013-10-27','2013-11-02',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
2,'20131103', '3605051772882', '2013-11-03','2013-11-09',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
3,'20131110', '3605051772882', '2013-11-10','2013-11-16',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
4,'20131117', '3605051772882', '2013-11-17','2013-11-23',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
5,'20131124', '3605051772882', '2013-11-24','2013-11-30',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
6,'20131201', '3605051772882', '2013-12-01','2013-12-07',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
7,'20131208', '3605051772882', '2013-12-08','2013-12-14',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
8,'20131215', '3605051772882', '2013-12-15','2013-12-21',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
9,'20131222', '3605051772882', '2013-12-22','2013-12-28',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
10,'20131229', '3605051772882', '2013-12-29','2014-01-04',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
11,'20140105', '3605051772882', '2014-01-05','2014-01-11',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
12,'20140112', '3605051772882', '2014-01-12','2014-01-18',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
13,'20140119', '3605051772882', '2014-01-19','2014-01-25',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
14,'20140126', '3605051772882', '2014-01-26','2014-02-01',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
15,'20140202', '3605051772882', '2014-02-02','2014-02-08',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
16,'20140209', '3605051772882', '2014-02-09','2014-02-15',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
drop table tb
go
create table tb(
id int,BatchID varchar(20),
BOMItem varchar(30),BeginDate datetime,EndDate datetime,
BeginningStock int,EndingStock int,VGUID varchar(36)
)
insert into tb
select 1,'20131027', '3605051925592', '2013-10-27','2013-11-02',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 2,'20131103', '3605051925592', '2013-11-03','2013-11-09',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 3,'20131110', '3605051925592', '2013-11-10','2013-11-16',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 4,'20131117', '3605051925592', '2013-11-17','2013-11-23',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 5,'20131124', '3605051925592', '2013-11-24','2013-11-30',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 6,'20131201', '3605051925592', '2013-12-01','2013-12-07',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 7,'20131208', '3605051925592', '2013-12-08','2013-12-14',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 8,'20131215', '3605051925592', '2013-12-15','2013-12-21',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 9,'20131222', '3605051925592', '2013-12-22','2013-12-28',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 10,'20131229', '3605051925592', '2013-12-29','2014-01-04',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 11,'20140105', '3605051925592', '2014-01-05','2014-01-11',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 12,'20140112', '3605051925592', '2014-01-12','2014-01-18',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 13,'20140119', '3605051925592', '2014-01-19','2014-01-25',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 14,'20140126', '3605051925592', '2014-01-26','2014-02-01',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 15,'20140202', '3605051925592', '2014-02-02','2014-02-08',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 16,'20140209', '3605051925592', '2014-02-09','2014-02-15',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 1,'20131027', '3605051772882', '2013-10-27','2013-11-02',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 2,'20131103', '3605051772882', '2013-11-03','2013-11-09',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 3,'20131110', '3605051772882', '2013-11-10','2013-11-16',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 4,'20131117', '3605051772882', '2013-11-17','2013-11-23',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 5,'20131124', '3605051772882', '2013-11-24','2013-11-30',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 6,'20131201', '3605051772882', '2013-12-01','2013-12-07',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 7,'20131208', '3605051772882', '2013-12-08','2013-12-14',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 8,'20131215', '3605051772882', '2013-12-15','2013-12-21',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 9,'20131222', '3605051772882', '2013-12-22','2013-12-28',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 10,'20131229', '3605051772882', '2013-12-29','2014-01-04',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 11,'20140105', '3605051772882', '2014-01-05','2014-01-11',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 12,'20140112', '3605051772882', '2014-01-12','2014-01-18',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 13,'20140119', '3605051772882', '2014-01-19','2014-01-25',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 14,'20140126', '3605051772882', '2014-01-26','2014-02-01',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 15,'20140202', '3605051772882', '2014-02-02','2014-02-08',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 16,'20140209', '3605051772882', '2014-02-09','2014-02-15',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
declare @sql nvarchar(max);
declare @i int;
set @sql = ''
set @i = 1
select @sql = @sql +
',max(case when begindate=''' +
convert(varchar(10),begindate,120) +'''' +
' then endingstock else 0 end) as week' + cast(@i as varchar),
@i = @i + 1
from tb
group by begindate
set @sql = 'select bomitem' + @sql + ' from tb group by bomitem'
select @sql
/*
select bomitem,
max(case when begindate='2013-10-27' then endingstock else 0 end) as week1,
max(case when begindate='2013-11-03' then endingstock else 0 end) as week2,
max(case when begindate='2013-11-10' then endingstock else 0 end) as week3,
max(case when begindate='2013-11-17' then endingstock else 0 end) as week4,
max(case when begindate='2013-11-24' then endingstock else 0 end) as week5,
max(case when begindate='2013-12-01' then endingstock else 0 end) as week6,
max(case when begindate='2013-12-08' then endingstock else 0 end) as week7,
max(case when begindate='2013-12-15' then endingstock else 0 end) as week8,
max(case when begindate='2013-12-22' then endingstock else 0 end) as week9,
max(case when begindate='2013-12-29' then endingstock else 0 end) as week10,
max(case when begindate='2014-01-05' then endingstock else 0 end) as week11,
max(case when begindate='2014-01-12' then endingstock else 0 end) as week12,
max(case when begindate='2014-01-19' then endingstock else 0 end) as week13,
max(case when begindate='2014-01-26' then endingstock else 0 end) as week14,
max(case when begindate='2014-02-02' then endingstock else 0 end) as week15,
max(case when begindate='2014-02-09' then endingstock else 0 end) as week16
from tb
group by bomitem
*/
exec(@sql)
/*
bomitemweek1week2week3week4week5week6week7week8week9week10week11week12week13week14week15week16
36050517728820000000000000000
36050519255920000000000000000
*/
create table u01
(id int,BatchID varchar(10),BOMItem varchar(15),
BeginDate varchar(15),EndDate varchar(15),BeginningStock varchar(10),EndingStock varchar(10),VGUID varchar(40))
insert into u01
select 1,'20131027','3605051925592','2013-10-27','2013-11-02',NULL,3,'010353CE-F18F-4324-9E0A-0091981EDD23' union all
select 2,'20131103','3605051925592','2013-11-03','2013-11-09',NULL,4,'010353CE-F18F-4324-9E0A-0091981EDD23' union all
select 3,'20131110','3605051925592','2013-11-10','2013-11-16',NULL,5,'010353CE-F18F-4324-9E0A-0091981EDD23' union all
select 4,'20131117','3605051925592','2013-11-17','2013-11-23',NULL,6,'010353CE-F18F-4324-9E0A-0091981EDD23' union all
select 5,'20131124','3605051925592','2013-11-24','2013-11-30',NULL,7,'010353CE-F18F-4324-9E0A-0091981EDD23' union all
select 6,'20131201','3605051925592','2013-12-01','2013-12-07',NULL,8,'010353CE-F18F-4324-9E0A-0091981EDD23' union all
select 7,'20131208','3605051925592','2013-12-08','2013-12-14',NULL,9,'010353CE-F18F-4324-9E0A-0091981EDD23' union all
select 8,'20131215','3605051925592','2013-12-15','2013-12-21',NULL,10,'010353CE-F18F-4324-9E0A-0091981EDD23' union all
select 9,'20131222','3605051925592','2013-12-22','2013-12-28',NULL,11,'010353CE-F18F-4324-9E0A-0091981EDD23' union all
select 10,'20131229','3605051925592','2013-12-29','2014-01-04',NULL,12,'010353CE-F18F-4324-9E0A-0091981EDD23' union all
select 11,'20140105','3605051925592','2014-01-05','2014-01-11',NULL,13,'010353CE-F18F-4324-9E0A-0091981EDD23' union all
select 12,'20140112','3605051925592','2014-01-12','2014-01-18',NULL,14,'010353CE-F18F-4324-9E0A-0091981EDD23' union all
select 13,'20140119','3605051925592','2014-01-19','2014-01-25',NULL,15,'010353CE-F18F-4324-9E0A-0091981EDD23' union all
select 14,'20140126','3605051925592','2014-01-26','2014-02-01',NULL,16,'010353CE-F18F-4324-9E0A-0091981EDD23' union all
select 15,'20140202','3605051925592','2014-02-02','2014-02-08',NULL,17,'010353CE-F18F-4324-9E0A-0091981EDD23' union all
select 16,'20140209','3605051925592','2014-02-09','2014-02-15',NULL,18,'010353CE-F18F-4324-9E0A-0091981EDD23' union all
select 1,'20131027','3605051772882','2013-10-27','2013-11-02',NULL,13,'BF98F7E6-6BB1-45A5-B350-062E872953F4' union all
select 2,'20131103','3605051772882','2013-11-03','2013-11-09',NULL,14,'BF98F7E6-6BB1-45A5-B350-062E872953F4' union all
select 3,'20131110','3605051772882','2013-11-10','2013-11-16',NULL,15,'BF98F7E6-6BB1-45A5-B350-062E872953F4' union all
select 4,'20131117','3605051772882','2013-11-17','2013-11-23',NULL,16,'BF98F7E6-6BB1-45A5-B350-062E872953F4' union all
select 5,'20131124','3605051772882','2013-11-24','2013-11-30',NULL,17,'BF98F7E6-6BB1-45A5-B350-062E872953F4' union all
select 6,'20131201','3605051772882','2013-12-01','2013-12-07',NULL,18,'BF98F7E6-6BB1-45A5-B350-062E872953F4' union all
select 7,'20131208','3605051772882','2013-12-08','2013-12-14',NULL,19,'BF98F7E6-6BB1-45A5-B350-062E872953F4' union all
select 8,'20131215','3605051772882','2013-12-15','2013-12-21',NULL,20,'BF98F7E6-6BB1-45A5-B350-062E872953F4' union all
select 9,'20131222','3605051772882','2013-12-22','2013-12-28',NULL,21,'BF98F7E6-6BB1-45A5-B350-062E872953F4' union all
select 10,'20131229','3605051772882','2013-12-29','2014-01-04',NULL,22,'BF98F7E6-6BB1-45A5-B350-062E872953F4' union all
select 11,'20140105','3605051772882','2014-01-05','2014-01-11',NULL,23,'BF98F7E6-6BB1-45A5-B350-062E872953F4' union all
select 12,'20140112','3605051772882','2014-01-12','2014-01-18',NULL,24,'BF98F7E6-6BB1-45A5-B350-062E872953F4' union all
select 13,'20140119','3605051772882','2014-01-19','2014-01-25',NULL,25,'BF98F7E6-6BB1-45A5-B350-062E872953F4' union all
select 14,'20140126','3605051772882','2014-01-26','2014-02-01',NULL,26,'BF98F7E6-6BB1-45A5-B350-062E872953F4' union all
select 15,'20140202','3605051772882','2014-02-02','2014-02-08',NULL,27,'BF98F7E6-6BB1-45A5-B350-062E872953F4' union all
select 16,'20140209','3605051772882','2014-02-09','2014-02-15',NULL,28,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
declare @tsql varchar(6000)
select @tsql=isnull(@tsql+',','')
+'[week'+rtrim(number)+']'
from master.dbo.spt_values
where type='P' and number between (select min(id) from dbo.u01) and (select max(id) from dbo.u01)
select @tsql='select BOMItem,'+@tsql
+' from (select BOMItem,''week''+rtrim(id) ''weekid'',EndingStock from u01) t '
+' pivot(max(EndingStock) for weekid in('+@tsql+')) p '
+' order by BOMItem desc '
exec(@tsql)
/*
BOMItem week1 week2 week3 week4 week5 week6 week7 week8 week9 week10 week11 week12 week13 week14 week15 week16
--------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
3605051925592 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
3605051772882 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
(2 row(s) affected)
*/
drop table tb
go
create table tb(
id int,BatchID varchar(20),
BOMItem varchar(30),BeginDate datetime,EndDate datetime,
BeginningStock int,EndingStock int,VGUID varchar(36)
)
insert into tb
select 1,'20131027', '3605051925592', '2013-10-27','2013-11-02',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 2,'20131103', '3605051925592', '2013-11-03','2013-11-09',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 3,'20131110', '3605051925592', '2013-11-10','2013-11-16',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 4,'20131117', '3605051925592', '2013-11-17','2013-11-23',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 5,'20131124', '3605051925592', '2013-11-24','2013-11-30',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 6,'20131201', '3605051925592', '2013-12-01','2013-12-07',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 7,'20131208', '3605051925592', '2013-12-08','2013-12-14',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 8,'20131215', '3605051925592', '2013-12-15','2013-12-21',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 9,'20131222', '3605051925592', '2013-12-22','2013-12-28',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 10,'20131229', '3605051925592', '2013-12-29','2014-01-04',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 11,'20140105', '3605051925592', '2014-01-05','2014-01-11',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 12,'20140112', '3605051925592', '2014-01-12','2014-01-18',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 13,'20140119', '3605051925592', '2014-01-19','2014-01-25',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 14,'20140126', '3605051925592', '2014-01-26','2014-02-01',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 15,'20140202', '3605051925592', '2014-02-02','2014-02-08',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 16,'20140209', '3605051925592', '2014-02-09','2014-02-15',NULL,NULL,'010353CE-F18F-4324-9E0A-0091981EDD23'
union all select 1,'20131027', '3605051772882', '2013-10-27','2013-11-02',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 2,'20131103', '3605051772882', '2013-11-03','2013-11-09',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 3,'20131110', '3605051772882', '2013-11-10','2013-11-16',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 4,'20131117', '3605051772882', '2013-11-17','2013-11-23',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 5,'20131124', '3605051772882', '2013-11-24','2013-11-30',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 6,'20131201', '3605051772882', '2013-12-01','2013-12-07',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 7,'20131208', '3605051772882', '2013-12-08','2013-12-14',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 8,'20131215', '3605051772882', '2013-12-15','2013-12-21',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 9,'20131222', '3605051772882', '2013-12-22','2013-12-28',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 10,'20131229', '3605051772882', '2013-12-29','2014-01-04',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 11,'20140105', '3605051772882', '2014-01-05','2014-01-11',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 12,'20140112', '3605051772882', '2014-01-12','2014-01-18',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 13,'20140119', '3605051772882', '2014-01-19','2014-01-25',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 14,'20140126', '3605051772882', '2014-01-26','2014-02-01',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 15,'20140202', '3605051772882', '2014-02-02','2014-02-08',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
union all select 16,'20140209', '3605051772882', '2014-02-09','2014-02-15',NULL,NULL,'BF98F7E6-6BB1-45A5-B350-062E872953F4'
declare @sql nvarchar(max);
declare @i int;
set @sql = ''
set @i = 1
select @sql = @sql +
',max(case when begindate=''' +
convert(varchar(10),begindate,120) +'''' +
' then endingstock else 0 end) as week' + cast(@i as varchar),
@i = @i + 1
from tb
group by begindate
set @sql = 'select bomitem' + @sql + ' from tb group by bomitem'
select @sql
/*
select bomitem,
max(case when begindate='2013-10-27' then endingstock else 0 end) as week1,
max(case when begindate='2013-11-03' then endingstock else 0 end) as week2,
max(case when begindate='2013-11-10' then endingstock else 0 end) as week3,
max(case when begindate='2013-11-17' then endingstock else 0 end) as week4,
max(case when begindate='2013-11-24' then endingstock else 0 end) as week5,
max(case when begindate='2013-12-01' then endingstock else 0 end) as week6,
max(case when begindate='2013-12-08' then endingstock else 0 end) as week7,
max(case when begindate='2013-12-15' then endingstock else 0 end) as week8,
max(case when begindate='2013-12-22' then endingstock else 0 end) as week9,
max(case when begindate='2013-12-29' then endingstock else 0 end) as week10,
max(case when begindate='2014-01-05' then endingstock else 0 end) as week11,
max(case when begindate='2014-01-12' then endingstock else 0 end) as week12,
max(case when begindate='2014-01-19' then endingstock else 0 end) as week13,
max(case when begindate='2014-01-26' then endingstock else 0 end) as week14,
max(case when begindate='2014-02-02' then endingstock else 0 end) as week15,
max(case when begindate='2014-02-09' then endingstock else 0 end) as week16
from tb
group by bomitem
