Single column output broken into multiple columns? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Single column output broken into multiple columns?

Author

Topic

macha_me
Starting Member

1 Post

Posted - 2011-07-12 : 09:51:15

Good morning!

Here is my query:

SELECT Reasons.Description
,COUNT (JnlTickets.PLU)
FROM JnlDetails JOIN JnlTickets
ON (JnlDetails.JnlDetailID = JnlTickets.JnlDetailID)
right JOIN JnlReasons
ON (JnlReasons.JnlTranID = JnlDetails.JnlTranID)
inner JOIN Reasons
ON (JnlReasons.ReasonID = Reasons.ID)
WHERE JnlDetails.JnlCodeID = 101
AND JnlTickets.PLU in ('TKT0019011','TKT0019012','TKT0019013')
GROUP BY Reasons.Description
,JnlDetails.JnlDetailID, JnlDetails.JnlTranID, JnlDetails.AuxTableID
Order By Reasons.Description

My output appears exactly like this:
Zoo Name Ticket Type
Abilene Zoological Gardens TKT0019011
Abilene Zoological Gardens TKT0019011
Abilene Zoological Gardens TKT0019011
Abilene Zoological Gardens TKT0019011
Abilene Zoological Gardens TKT0019012
Akron Zoological Park TKT0019011
Akron Zoological Park TKT0019011
Akron Zoological Park TKT0019011
Akron Zoological Park TKT0019012
Akron Zoological Park TKT0019012
Akron Zoological Park TKT0019011

The data output is what i want but the format in which i want it off. This is how I want it to appear:

Zoo Name Adults Child Senior
Zoo A 5 2 0
Zoo B 1 4 1
Zoo C 0 6 10
Zoo D 5 2 9

Therefore, I want to add ticket types based on the zoo and within each zoo i want to add the different ticket types.

I am taking my query and using in SSRS2005 for a report so perhaps i can massage the query in SSRS. But i trid in both places and I can get the output i expect.

Can someone please guide me?

Thanks,
Macha

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-12 : 10:36:06

I don't think that anyone can understand this...

SELECT Reasons.Description,COUNT (JnlTickets.PLU)

gives you

Zoo Name Ticket Type
Abilene Zoological Gardens TKT0019011
...

Never!

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources