Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
700
Column Grouping....Need solution
posted

I have records as below

Country  Color        Men Women
India       RED         5        6
India       Blue         10      12
USA       RED         7        7
USA       GREEN    6         8
UK         Blue         5         9
UK         RED        12        18

Need to produce the following output..

                  RED                 BLUE              GREEN
Country  Men Women       Men Women     Men Women
India       5        6               10      12           0      0
USA       7        7                0       0             6      8
UK         5        9                5       8             0      0

 

Can someone help me to solve this ?

Parents
  • 670
    posted

    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.

     

     

     

     

Reply Children
No Data