Need help in query - 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

Need help in query

Author

Topic

learntsql

524 Posts

Posted - 2009-07-18 : 02:01:15

Hi All,
me query returns following output

Stage-Count-State-Category
S1-50-MP-C1
S2-20-MP-C2
S3-30-MP-C1
S4-20-MP-C2
S5-10-MP-C1

and i have to show summary based on this data as

Stage1 | Stage2
-------- ------ like that for all stages
C1|C2 C1|C2

for example
Stage1
C1|C2
50|30

Note:Here Stage1 is S1+S2
and State column contains morethan 15 states
Stage Names and CategoryNames are Standerd.
I hope am clear.

Please tell me how to do that.
i am trying with pivot but not getting exact output,
please guide me.
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-18 : 02:05:33

is this for reporting need? what reporting tool are you using?

learntsql

524 Posts

Posted - 2009-07-18 : 02:11:12

Yes visakh16,
its for Report.
I am using SSRS 2005.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-18 : 02:15:40

then you can very easily get this output by using a matrix container. just give the column group as stage and category. also when you write back end query make sure you bring the stage value as a derived column like below


SELECT CASE WHEN Stage IN ('S1','S2') THEN 'Stage1'
            WHEN Stage IN ('S3','S4',..) THEN 'Stage2'
       END AS StageVal,
       Count,State,Category,...
FROM Table

then assign the dataset to matrix and group on StageVal and Category (column grouping)

learntsql

524 Posts

Posted - 2009-07-18 : 02:25:52

ThanQ veru much Visakh,
I will do it.
Thanks again.

learntsql

524 Posts

Posted - 2009-07-18 : 03:29:50

If I get NULL values in StageVal how to filter them and
can you please guide me in design matrix report becoz this is my first matrix report.
I am getting Stage1 and Stage2 (stages values) are getting repeating.
ThankQ.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources