Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Convert Rows as column(but have Multiple condition

Author  Topic 

pkindigo
Starting Member

2 Posts
Posted - 2011-05-05 : 08:06:59
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 types saleitemunitprice saletotalquantity saletotalsaleprice
1062 180 BOM212 150 90 4500
1099 0 BOM212 130 31 4030
1100 0 BOM212 150 32 4800
1101 0 BOM212 50 32 1600
1102 0 BOM212 50 32 1600
1103 0 BOM212 100 31 3100
1104 0 BOM212 150 9 1350
1105 0 BOM212 130 9 1170
1106 0 BOM212 130 9 1170
1107 0 BOM212 150 9 1350
1108 0 BOM212 50 10 500
1099 0 BOM569 130 57 7410
1100 0 BOM569 150 55 8250
1101 0 BOM569 50 55 2750
1102 0 BOM569 50 58 2900


and i have to display the output like

ItemId DEL569 CCU569 BOM569 BOM212 saleitemunitprice saletotalquantity saletotalsaleprice
1105 5 4 6 0 10 10 100
1106 5 4 6 0 10 10 100
1108 5 4 6 0 10 10 100
1109 5 4 6 0 10 10 100
1110 5 4 6 0 10 10 100
1111 5 4 6 0 10 10 100

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.

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2011-05-05 : 08:26:09
What are those values, counts? Nothing to do with the sample data?

Jim

P.S. Thank you for the create table and sample data, so many people just don't do that!

Everyday I learn something that somebody else already knew
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site