CODEWITHSUNDEEP
sqlsql-server-2008
Y2theZ
Y2theZ

Reputation: 10412

SQL script to change the structure of the table

I am working on a SQL database (Microsoft SQL Server 2008)

I have a table like this

Country     Gender           Number         
---------+---------------+-----------+
Russia       Male              50000                
Russia       Female            40000 
Russia       Unknown           30000
India        Male              45678                
India        Female            21354  
China        Male              37878                
China        Female            45686
China        Unknown           24534
France       Male              45378                
France       Female            49783

Is there an sql select script that can return a table like this:

    Country      Male             Female      Unknown   
---------+---------------+-----------+-----------------+
Russia       50000            40000        30000             
India        45678            21354        0
China        37878            45686        24534
France       45378            49783        0

Please note that some countries do not have "Unkown" data so in this case it should be 0.

I tried a couple of things but I did not get any close results. Thanks for any help.

Upvotes: 1

Views: 77

Answers (2)

roman
roman

Reputation: 117380

you can also use this query

select
    T.Country,
    max(case when T.Gender = 'Male' then T.Number else 0 end) as Male,
    max(case when T.Gender = 'Female' then T.Number else 0 end) as Female,
    max(case when T.Gender = 'Unknown' then T.Number else 0 end) as Unknown
from Table1 as T
group by T.Country

sql fiddle demo

Upvotes: 1

i-one
i-one

Reputation: 5120

You can use pivot operator for such kind of queries:

select Country,
    IsNull([Male], 0) Male,
    IsNull([Female], 0) Female,
    IsNull([Unknown], 0) Unknown
from TableName t
   pivot (sum(Number) for Gender in ([Male], [Female], [Unknown])) p

or ... pivot max(Number) for ... if you know that (Country, Gender) combination is unique in original data.

Upvotes: 2

Related Questions