Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Count Summary Expand / Collapse
Author
Message
Posted Thursday, August 26, 2010 3:55 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 18, 2013 1:23 PM
Points: 90, Visits: 286
I am considering ways to achieve the following sample desired output given the sample input:

declare @t1 table
(customerid int,
city varchar (50),
zip varchar(15),
LOB varchar(4),
ServiceCount int)

Insert into @t1 values( 100,'Dallas', '75287','PW', 14)
Insert into @t1 values( 102, 'Dallas', '75287','PW', 11)
Insert into @t1 values( 104,'Dallas', '75100','PW', 4)
Insert into @t1 values( 200,'Dallas', '75287','CWS', 1)
Insert into @t1 values( 300,'Dallas', '75287','IMG', 3)
Insert into @t1 values( 400,'GraysLake', '60030','CWS', 4)
Insert into @t1 values( 401,'GraysLake', '60030','CWS', 2)
Insert into @t1 values( 400,'GraysLake', '60030','PW', 3)
Insert into @t1 values( 403,'GraysLake', '60030','PW', 3)

select * from @t1

-- desired output
-- City ZIP PW CWS IMG
-- Dallas 75287 25 1 3
-- Dallas 75100 4 0 0
-- GraysLake 60030 6 6 0

The LOB are fixed ( they are predefined).

My options are:
1. Make several passes to get the desired output using temp tables or table variables. The stored proc will be lengthy and not easy to read.

2. Possibly use CTE and pivot??? This is where I am lost.

Any suggestions or guidance will be much appreciated. Thank you much in advance.
Post #975987
Posted Thursday, August 26, 2010 4:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 6,928, Visits: 12,644
WOW!!!!
It doesn't happen that often to have somebody posting a question as perfect as you did!!!
There's nothing more to say than GREAT JOB!!
And here's how I'd do it:

SELECT 
city,
zip,
SUM(CASE WHEN LOB='PW' THEN ServiceCount ELSE 0 END) AS PW,
SUM(CASE WHEN LOB='CWS' THEN ServiceCount ELSE 0 END) AS CWS,
SUM(CASE WHEN LOB='IMG' THEN ServiceCount ELSE 0 END) AS IMG
FROM @t1
GROUP BY city,zip
ORDER BY city,zip DESC

The concept is based on the CrossTab article referenced in my signature. Once you're familiar with the concept I recommend to have a look at the DynamicCrossTab article also referenced in my sig. It might come in handy if you ever need to make that query using a flexible number of LOB values....




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #976003
Posted Friday, August 27, 2010 7:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 18, 2013 1:23 PM
Points: 90, Visits: 286
Thank you very much for your guidance and assistance. It works perfectly.

As for the way I ask questions, I was once kindly and gently trained by this forum to format their content to facilitate the possibility of quickly getting an answer.

Many thanks again!
Post #976385
Posted Friday, August 27, 2010 7:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:59 AM
Points: 6,544, Visits: 8,761
MissyDaisy (8/27/2010)
As for the way I ask questions, I was once kindly and gently trained by this forum to format their content to facilitate the possibility of quickly getting an answer.


And it worked! In fact, it worked so well that your post was used as an example of how to do it on another post!


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #976411
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse