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 |
|
vamsimahi
Starting Member
29 Posts |
 Posted - 2008-08-18 : 20:49:03
|
| Hi,I have a table with four columns CUSTOMER_ID int,PRODUCT varchar, PRODUCT_SHARE numeric, PRODUCT_CHANGE numeric. I have three products ProdA, ProbB, ProbC . I want to move the product values into columns like ProdA_PRODUCT_SHARE, ProdA_PRODUCT_CHANGE, ProdB_PRODUCT_SHARE, ProdB_PRODUCT_CHANGE,ProdC_PRODUCT_SHARE,ProdC_PRODUCT_CHANGE .How can I pivot with two columns PRODUCT_SHARE, PRODUCT_CHANGE ? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-08-18 : 20:54:29
|
use the CASE WHEN . . .way or PIVOT it twice (one for SHARE and one for CHANGE) and inner join together.
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-18 : 23:57:48
|
| [code]something like below:-SELECT CUSTOMER_ID,MAX(CASE WHEN PRODUCT ='ProdA' THEN PRODUCT_SHARE ELSE NULL END) AS ProdA_PRODUCT_SHARE,MAX(CASE WHEN PRODUCT ='ProdA' THEN PRODUCT_CHANGE ELSE NULL END) AS ProdA_PRODUCT_CHANGE ,MAX(CASE WHEN PRODUCT ='ProdB' THEN PRODUCT_SHARE ELSE NULL END) AS ProdB_PRODUCT_SHARE,MAX(CASE WHEN PRODUCT ='ProdB' THEN PRODUCT_CHANGE ELSE NULL END) AS ProdB_PRODUCT_CHANGE ,MAX(CASE WHEN PRODUCT ='ProdC' THEN PRODUCT_SHARE ELSE NULL END) AS ProdC_PRODUCT_SHARE,MAX(CASE WHEN PRODUCT ='ProdC' THEN PRODUCT_CHANGE ELSE NULL END) AS ProdC_PRODUCT_CHANGE FROM YourTableGROUP BY CUSTOMER_ID[/code] |
|
|
|
vamsimahi
Starting Member
29 Posts |
Posted - 2008-08-19 : 10:04:31
|
| ThanksKH and Visakh, i was able to do it in both the ways... |
|
|
|
|
|
|
|
|
pivot on two columns...