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.
| Author |
Topic |
|
christiangodi
Starting Member
3 Posts |
 Posted - 2010-05-10 : 23:24:42
|
| Hi, i have the following requirement in sqli have a table as followsID OPPID halfyear unitprice quantity 1 DL-201001 1HFY10 10 3 2 DL-201001 2HFY10 5 4 3 IN-201002 1HFY10 11 1 4 IN-201002 2HFY10 13 2 5 IN-201002 3HFY10 2 100Now i need a select query on the above table to be reported as followsoppid unitprice quantity unitprice quantity unitprice quantity DL-201001 10 3 5 4IN-201002 11 1 13 2 2 100your help is much appreciated |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2010-05-11 : 00:05:46
|
[code]select OPPID ,max(case when halfyear='1HFY10' then unitprice else null end) as unitprice_1HFY10 ,max(case when halfyear='1HFY10' then quantity else null end) as quantity_1HFY10 ,max(case when halfyear='2HFY10' then unitprice else null end) as unitprice_2HFY10 ,max(case when halfyear='2HFY10' then quantity else null end) as quantity_2HFY10 ,max(case when halfyear='3HFY10' then unitprice else null end) as unitprice_3HFY10 ,max(case when halfyear='3HFY10' then quantity else null end) as quantity_3HFY10 -- add more rows here if there are other values of halfyear you care about from YourTable group by OPPID order by OPPID[/code]search for "crosstab" on this site for other examples.
elsasoft.org |
 |
|
|
christiangodi
Starting Member
3 Posts |
Posted - 2010-05-11 : 01:13:59
|
quote:
Originally posted by jezemine
select OPPID ,max(case when halfyear='1HFY10' then unitprice else null end) as unitprice_1HFY10 ,max(case when halfyear='1HFY10' then quantity else null end) as quantity_1HFY10 ,max(case when halfyear='2HFY10' then unitprice else null end) as unitprice_2HFY10 ,max(case when halfyear='2HFY10' then quantity else null end) as quantity_2HFY10 ,max(case when halfyear='3HFY10' then unitprice else null end) as unitprice_3HFY10 ,max(case when halfyear='3HFY10' then quantity else null end) as quantity_3HFY10 -- add more rows here if there are other values of halfyear you care about from YourTable group by OPPID order by OPPID search for "crosstab" on this site for other examples.
elsasoft.org
a million thanks...that worked out for me |
|
|
|
|
|
|
|
|
collapsing multiple rows into one row