Subquery problem - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Subquery problem

Author

Topic

ram2011
Starting Member

1 Post

Posted - 2011-11-07 : 01:00:38

hi frnds,

I am using SQLSERVER, pls help me in this query...,my scenario is like this..,i ma having a table with columns
country_id,state_id,parliament_id,assembly_id,race.

the table data is like this
1 1 1 1 indian
1 1 2 1 indian
1 2 1 1 chinese
1 3 1 1 malay

now i want the select data in this way

country_id state_id parliament_id assmbly_id indian chinese mal
1 1 1 1 1 0 0
1 1 2 1 1 0 0
1 2 1 1 0 1 0
1 3 1 1 0 0 1

pls help me ASAP...i have written a query in this way..,but its coming wrong...so pls help me

select country_id,state_id,parliamnet_id,assembly_id,
(select sum(race) from table where race='indian') as indian,
(select sum(race) from table where race='chinese') as chinese,
(select sum(race) from table where race='malay') as mal,
from table group by country_id,state_id,parliamnet_id,assembly_id.

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-11-07 : 01:16:48

Hi,

You need to rotate your result table, In this case refer following link which explain how to implement pivot to rotate your table:
http://msdn.microsoft.com/en-us/library/ms177410.aspx
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 04:19:20

quote:
Originally posted by jassi.singh

Hi,

You need to rotate your result table, In this case refer following link which explain how to implement pivot to rotate your table:
http://msdn.microsoft.com/en-us/library/ms177410.aspx
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

no need of pivot at all just below would do it


select country_id,state_id,parliament_id,assembly_id,
case when race='indian' then 1 else 0 end as indian,
case when race='chinese' then 1 else 0 end as chinese,
case when race='malay' then 1 else 0 end as mal
from table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources