April 23, 2010 at 12:05 pm
I have the following table (TEST) with 5 columns (PID, Comp1, Comp2, Comp3, Comp4).
PIDComp1Comp2Comp3Comp4
1EQUALDIFFEQUALLOSS
2EQUALNULLGAINEQUAL
3LOSSGAINNULLEQUAL
4GAINEQUALEQUALEQUAL
5DIFFDIFFNULLGAIN
Desired result:
ResultCount1Count2Count3Count4
GAIN1111
LOSS1001
DIFF1200
NULL0120
EQUAL2123
Any help is much appreciated. TIA
April 23, 2010 at 4:51 pm
You could use UNPIVOT followed by a PIVOT statement. See BOL (BooksOnLine, the SQL Server help system usually installed together with SQL Server) for details or post some ready to use sample data as described in the first link in my signature so we have something to test against.
April 25, 2010 at 11:14 pm
Hi Gerald... I would suggest you to look thro the first link in the signature line of Lutz.. that will make the volunteers here address your request easily and give back you tested and optimized code..
Off late i am havign some free time i took up your request and worked on it..
Here are the DLLs of tables and sample data, and the query for your desired output..
-- Suppress COUNT of ROWS AFFECTED to improve performance
SET NOCOUNT ON
/*
Drop table if it already exists
*/
IF OBJECT_ID('TEMPDB..#TestResults') IS NOT NULL
DROP TABLE #TestResults
/*
Create a temporary table to hold data for the request
*/
CREATE TABLE #TestResults
(
PID INT,
Comp1 VARCHAR(10),
Comp2 VARCHAR(10),
Comp3 VARCHAR(10),
Comp4 VARCHAR(10)
)
/*
Populate temporary table with certain number sample records
*/
INSERT INTO #TestResults
SELECT 1, 'EQUAL', 'DIFF', 'EQUAL' , 'LOSS' UNION ALL
SELECT 2, 'EQUAL', NULL, 'GAIN' , 'EQUAL' UNION ALL
SELECT 3, 'LOSS', 'GAIN' ,NULL, 'EQUAL' UNION ALL
SELECT 4, 'GAIN', 'EQUAL' ,'EQUAL', 'EQUAL' UNION ALL
SELECT 5, 'DIFF', 'DIFF' ,NULL, 'GAIN'
-- Just to check how the table looks like
-- Note here, i am avoding using "SELECT * FROM " which is good practice
SELECT PID ,Comp1,Comp2,Comp3,Comp4 FROM #TestResults
--=== The query that will work for your requirement
;WITH UNPIVOT_DATA_CTE (COMP, Test_Result , [COUNT] )
AS
(
-- First UNPIVOT the result values
SELECT
COMP, Test_Result , COUNT(*) [COUNT]
FROM
( SELECT PID,
ISNULL(Comp1 ,'NULL') Comp1,
ISNULL(Comp2 ,'NULL') Comp2,
ISNULL(Comp3 ,'NULL') Comp3,
ISNULL(Comp4 ,'NULL') Comp4
FROM #TestResults ) UNPIVOT_TABLE
UNPIVOT
( Test_Result FOR COMP IN (Comp1, Comp2,Comp3,Comp4) ) UNPIVOT_HANDLE
GROUP BY
COMP , Test_Result
)
-- Pivot it back to match the output
SELECT
Test_Result Result,
ISNULL([Comp1] ,0) Comp1Count,
ISNULL([Comp2] ,0) Comp2Count,
ISNULL([Comp3] ,0) Comp3Count,
ISNULL([Comp4] ,0) Comp4Count
FROM
(SELECT COMP, Test_Result , [COUNT] FROM UNPIVOT_DATA_CTE) PIVOT_TABLE
PIVOT
(MAX([COUNT]) FOR COMP IN ([Comp1],[Comp2],[Comp3],[Comp4])) PIVOT_HANDLE
Hope this helps you...Tell us here if the above code helped you!
Cheers!
C'est Pras!!
April 25, 2010 at 11:20 pm
Also i have another gift for you 😛
I slightly altered the pivot values in the query and i have got another well looking output.. This may be of some help for u in the future..
Note: I am using the same input data that i provided in the earlier post
Now, the code
;WITH UNPIVOT_DATA_CTE (COMP, Test_Result)
AS
(
SELECT
COMP, Test_Result
FROM
( SELECT PID,
ISNULL(Comp1 ,'NULL') Comp1,
ISNULL(Comp2 ,'NULL') Comp2,
ISNULL(Comp3 ,'NULL') Comp3,
ISNULL(Comp4 ,'NULL') Comp4
FROM #TestResults ) UNPIVOT_TABLE
UNPIVOT
( Test_Result FOR COMP IN (Comp1, Comp2,Comp3,Comp4) ) UNPIVOT_HANDLE
)
SELECT COMP,[EQUAL],[DIFF],[NULL],[LOSS],[GAIN] FROM
(SELECT COMP, Test_Result FROM UNPIVOT_DATA_CTE) PIVOT_TABLE
PIVOT
(COUNT(Test_Result) FOR Test_Result IN ([EQUAL],[DIFF],[NULL],[LOSS],[GAIN])) PIVOT_HANDLE
And the new desired output will look like
COMPEQUALDIFFNULLLOSSGAIN
Comp121011
Comp212101
Comp320201
Comp430011
April 26, 2010 at 7:52 am
ColdCoffee,
You ARE a genius!! The results of the second change was exactly what I was looking for. I cannot thank you enough for your generous assistance to my problem.
Have a great day! 🙂
Gerald
April 26, 2010 at 8:05 am
Happy to have helped you buddy 🙂
April 26, 2010 at 8:29 am
April 26, 2010 at 9:26 am
ColdCoffee,
When I add more Comp99 fields to the existing code, the pivot results are shown in a sorted sequence.
Comp1
Comp10
Comp2
Comp3
...
Is there a way to eliminate the sorting part of this statement?
Thanks,
Gerald
April 26, 2010 at 9:39 am
If you want to sort them by Number part of the string (i.e. Comp5 comes before Comp10) you could rename the alias names slightly when you do your pivot to Compxx format, so Comp1 becomes Comp01 and so on.
;WITH UNPIVOT_DATA_CTE (COMP, Test_Result)
AS
(
SELECT
COMP, Test_Result
FROM
( SELECT PID,
ISNULL(Comp1 ,'NULL') Comp01 --change alias to Compxx,
ISNULL(Comp2 ,'NULL') Comp02,
ISNULL(Comp3 ,'NULL') Comp03,
ISNULL(Comp4 ,'NULL') Comp04,
ISNULL(Comp10 ,'NULL') Comp10
FROM #TestResults ) UNPIVOT_TABLE
UNPIVOT
( Test_Result FOR COMP IN (Comp01, Comp02,Comp03,Comp04,Comp10) ) UNPIVOT_HANDLE
)
SELECT COMP,[EQUAL],[DIFF],[NULL],[LOSS],[GAIN] FROM
(SELECT COMP, Test_Result FROM UNPIVOT_DATA_CTE) PIVOT_TABLE
PIVOT
(COUNT(Test_Result) FOR Test_Result IN ([EQUAL],[DIFF],[NULL],[LOSS],[GAIN])) PIVOT_HANDLE
Result should be:
COMPEQUALDIFFNULLLOSSGAIN
Comp0121011
Comp0212101
Comp0320201
Comp0430011
Comp1000060
April 26, 2010 at 9:47 am
That worked fine, Dohsan. Thanks much!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply