August 26, 2010 at 3:55 pm
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.
August 26, 2010 at 4:19 pm
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....
August 27, 2010 at 7:39 am
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!
August 27, 2010 at 7:57 am
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
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply