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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Nedd SQL Query

Author  Topic 

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2010-08-12 : 03:34:07
Table Contains following data and Strcuture
Region Subcategory Status
North Sub1 Closed
Sub2 Open
Sub3 Open
I want Query for below result

Region Sub1 Sub2 Sub3
North Closed Open Open

Need help

V.NAGARAJAN

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-08-12 : 04:31:56
Hi,

This can be done using Pivot operator.

Select region,[Sub1],[Sub2],[Sub3] From
(Select Region,Subcategory, Status
from MyTable) as SubTab
Pivot
(
Max(Status) for Subcategory
in (
[Sub1],[Sub2],[Sub3])) as Pvt



Also have a look at dynamic pivot at :
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-12 : 11:28:01
will it be always 3 subs or can it vary?

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

Go to Top of Page

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2010-08-13 : 01:38:12
quote:
Originally posted by visakh16

will it be always 3 subs or can it vary?

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





Vary


V.NAGARAJAN
Go to Top of Page

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-08-13 : 01:46:08
quote:
Originally posted by itnagaraj

quote:
Originally posted by visakh16

will it be always 3 subs or can it vary?

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





Vary

V.NAGARAJAN



then you need to use dynamic pivot:

http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
Go to Top of Page
   

- Advertisement -