I have records as below
Country Color Men WomenIndia RED 5 6India Blue 10 12USA RED 7 7USA GREEN 6 8UK Blue 5 9UK RED 12 18
Need to produce the following output..
RED BLUE GREENCountry Men Women Men Women Men WomenIndia 5 6 10 12 0 0USA 7 7 0 0 6 8UK 5 9 5 8 0 0
Can someone help me to solve this ?
Thanks jh72i,
I am using SQL Server and I did as below
************************************
sum(RedMale), sum(RedFemale),
sum(BlueMale), sum(BlueFemale),
sum(GreenMale), sum(GreenFemale)
from
(
case Color When 'RED' then Male End 'RedMale',
case Color When 'RED' then Female End 'RedFemale',
case Color When 'Blue' then Male End 'BlueMale',
case Color When 'Blue' then Female End 'BlueFemale',
case Color When 'GREEN' then Male End 'GreenMale',
case Color When 'GREEN' then Female End 'GreenFemale'
from temp) a group by country order by Country
Result:
India 5 6 10 12 NULL NULLUK 12 18 5 9 NULL NULLUSA 7 7 NULL NULL 6 8
**********************************
This could only work if we know in advance of the possible values of the column Color. If not then we need to dynalically construct as explained in the article.
I have similar challenges but with a lot more columns. for you're problem there is a good article here: http://www.codeproject.com/KB/database/Pivot2Columns.aspx?fid=1532143&df=90&mpp=25&noise=3&sort=Position&view=Quick&select=2844387
You could also use a simple pivot query twice (once for each item) and join them.
You could also try what I tried and create a relationship in the data so that each extra group of columns is actually a band (child band of Red would be Blue, child band of Blue would be Green.), then display the bands horizontally, ensure they are all expanded, get rid of +/- buttons, etc. This actually worked well for small datasets but for large ones it bombs when calling ExpandAll.
Final alternative (which the one I'm stuck with right now) is to try to turn the vertical dataset into a horizontal one manually using a datatable and use groups to manage the column groupings.
If you figure out a better way please do post.