Click here to Skip to main content
15,077,377 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Sir,
I have a table in this format.
C#
class subject no_of_boys no_of_girls
1         A       40        60
1         B       50        70
1         C       80        50
2         A       55        40
2         B       57        65
2         C       54        29
3         A       42        14
3         B       68        53
3         C       40        10

Need in this format.
C#
CLASS A_BOYS A_GIRLS B_BOYS B_GIRLS C_BOYS C_GIRLS
1      40      60      50      70     80     50
2      55      40      57      65     54     29
3      42      14      68      53     40     10

Please help.
Posted

1 solution

Try this:
SQL
SELECT Class, A_BOYS, A_GIRLS, B_BOYS, B_GIRLS, C_BOYS, C_GIRLS
FROM (
    SELECT  Class, CONVERT(NVARCHAR(30), subject + '_Boys') Category, no_of_boys AS [Population]
    FROM Tbl_Class
    UNION ALL
    SELECT  Class, CONVERT(NVARCHAR(30), subject + '_Girls'), no_of_girls AS [Population]
    FROM Tbl_Class
    ) AS DT
PIVOT (SUM([Population]) FOR Category IN (A_BOYS, A_GIRLS, B_BOYS, B_GIRLS, C_BOYS, C_GIRLS)) AS PT


If you want to do it in dynamic way, plese follow this link: https://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/[^].
   
v2
Comments
damodara naidu betha 19-Jun-13 2:25am
   
Good one 5+
Maciej Los 19-Jun-13 8:24am
   
Thank you ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900