May 5, 2011 at 3:47 am
hi,
I have to convert the rows data as column name and find sum of items .Please help.
create table TT(itemid varchar(50),upload int
,Types varchar(50),saleitemunitprice int,saletotalquantity int
,saletotalsaleprice int)
insert into TT values ('1062',180,'BOM212',150,90,4500)
insert into TT values ('1099',0,'BOM212',130,31,4030)
insert into TT values ('1100',0,'BOM212',150,32,4800)
insert into TT values ('1101',0,'BOM212',50,32,1600)
insert into TT values ('1102',0,'BOM212',50,32,1600)
insert into TT values ('1103',0,'BOM212',100,31,3100)
insert into TT values ('1104',0,'BOM212',150,9,1350)
insert into TT values ('1105',0,'BOM212',130,9,1170)
insert into TT values ('1106',0,'BOM212',130,9,1170)
insert into TT values ('1107',0,'BOM212',150,9,1350)
insert into TT values ('1108',0,'BOM212',50,10,500 )
insert into TT values ('1109',0,'BOM212',130,9,1170)
insert into TT values ('1110',0,'BOM212',150,11,1650)
insert into TT values ('1111',0,'BOM212',100,9,900 )
insert into TT values ('1112',0,'BOM212',30,7,210 )
insert into TT values ('1113',0,'BOM212',50,7,350 )
insert into TT values ('1114',0,'BOM212',50,7,350)
insert into TT values ('1115',0,'BOM212',50,7,350)
insert into TT values ('1116',0,'BOM212',50,7,350)
insert into TT values ('1232',16,'BOM212',0,0,0)
insert into TT values ('1233',8,'BOM212',0,0,0)
insert into TT values ('1234',72,'BOM212',0,0,0)
insert into TT values ('1235',20,'BOM212',0,0,0)
insert into TT values ('1977',10,'BOM212',0,0,0)
insert into TT values ('1978',12,'BOM212',0,0,0 )
insert into TT values ('1982',48,'BOM212',0,0,0)
insert into TT values ('1984',10,'BOM212',0,0,0)
insert into TT values ('1099',0,'BOM569',130,57,7410)
insert into TT values ('1100',0,'BOM569',150,55,8250)
insert into TT values ('1101',0,'BOM569',50,55,2750)
insert into TT values ('1102',0,'BOM569',50,58,2900)
insert into TT values ('1103',0,'BOM569',100,56,5600)
insert into TT values ('1104',0,'BOM569',150,12,1800)
insert into TT values ('1105',0,'BOM569',130,18,2340)
insert into TT values ('1106',0,'BOM569',130,13,1690)
insert into TT values ('1107',0,'BOM569',150,12,1800)
insert into TT values ('1108',0,'BOM569',50,23,1150)
insert into TT values ('1109',0,'BOM569',130,12,1560)
insert into TT values ('1110',0,'BOM569',150,14,2100)
insert into TT values ('1111',0,'BOM569',100,9,900 )
insert into TT values ('1112',0,'BOM569',30,4,120)
insert into TT values ('1113',0,'BOM569',50,4,200)
insert into TT values ('1114',0,'BOM569',50,4,200)
insert into TT values ('1115',0,'BOM569',50,4,200)
insert into TT values ('1116',0,'BOM569',50,6,300)
insert into TT values ('1103',0,'CCU569',100,1,100)
insert into TT values ('1105',0,'CCU569',130,1,130)
insert into TT values ('1106',0,'CCU569',130,4,520)
insert into TT values ('1108',0,'CCU569',50,1,50 )
insert into TT values ('1109',0,'CCU569',130,6,780 )
insert into TT values ('1110',0,'CCU569',150,2,300 )
insert into TT values ('1111',0,'CCU569',100,5,500 )
insert into TT values ('1099',0,'DEL569',130,2,260 )
insert into TT values ('1100',0,'DEL569',150,1,150 )
insert into TT values ('1101',0,'DEL569',50,2,100 )
insert into TT values ('1102',0,'DEL569',50,3,150 )
insert into TT values ('1103',0,'DEL569',100,2,200)
insert into TT values ('1104',0,'DEL569',150,2,300)
insert into TT values ('1105',0,'DEL569',130,6,780)
insert into TT values ('1106',0,'DEL569',130,3,390)
insert into TT values ('1107',0,'DEL569',150,2,300)
insert into TT values ('1108',0,'DEL569',50,4,200)
insert into TT values ('1109',0,'DEL569',130,3,390)
insert into TT values ('1110',0,'DEL569',150,4,600)
insert into TT values ('1111',0,'DEL569',100,4,400)
insert into TT values ('1112',0,'DEL569',30,1,30)
insert into TT values ('1113',0,'DEL569',50,1,50)
insert into TT values ('1114',0,'DEL569',50,1,50)
insert into TT values ('1115',0,'DEL569',50,1,50)
insert into TT values ('1116',0,'DEL569',50,1,50)
insert into TT values ('1117',0,'DEL569',590,1,590)
insert into TT values ('1118',0,'DEL569',750,1,750)
insert into TT values ('1119',0,'DEL569',19,1,19 )
insert into TT values ('1120',0,'DEL569',650,1,650)
insert into TT values ('1121',0,'DEL569',650,1,650)
insert into TT values ('1124',0,'DEL569',1090,1,1090)
insert into TT values ('1125',0,'DEL569',2690,1,2690)
insert into TT values ('1150',0,'DEL569',890,1,890)
select * from TT
now the data is like this.
itemid upload typessaleitemunitpricesaletotalquantitysaletotalsaleprice
1062180BOM212150904500
10990BOM212130314030
11000BOM212150324800
11010BOM21250321600
11020BOM21250321600
11030BOM212100313100
11040BOM21215091350
11050BOM21213091170
11060BOM21213091170
11070BOM21215091350
11080BOM2125010500
10990BOM569130577410
11000BOM569150558250
11010BOM56950552750
11020BOM56950582900
and i have to display the output like
ItemId DEL569 CCU569 BOM569 BOM212 saleitemunitpricesaletotalquantitysaletotalsaleprice
1105 5 4 6 0 1010100
1106 5 4 6 0 1010100
1108 5 4 6 0 1010100
1109 5 4 6 0 1010100
1110 5 4 6 0 1010100
1111 5 4 6 0 1010100
DEL569 CCU569 BOM569 BOM212 all are the types which has been converted as a column and upload is the total sum of items display under these column. and all
other column are the sum of items.
May 5, 2011 at 8:33 am
Your results don't match the sample data. I looked at itemid 1105 and I'm not sure where your numbers are coming from. I also don't understand what results you are expecting. Why are the types treated as columns and what do the numbers represent? Even the sum totals don't make sense, so I'm not sure what you are expecting
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply