November 13, 2007 at 11:54 am
I am having a hard time getting a pivot table to work correctly. One problem is that it is not formatting the ouput corretly, the other problem is that my very small development dataset is producing dynamic sql that is way too big.
My question here is in two parts:
1. How can I get the output generated correctly for my sample dataset?
2. How can I get the dynamic sql size cut down to handle my production dataset?
Pivot Description:
I have a table which contains test features, and test run status'. I need to pivot the run status' so that on the left column, we have the feature, and in the right columns we have the percentage of the feature in a given run status. My sample set contains 6 "Feature" and 6 "RunStatus". The production DB table contains ~1700 "Feature" and ~60 "RunStatus".
Sample Dataset:
DECLARE@raw TABLE (TestListID INT, Feature VARCHAR(256), RunStatus varchar(64))
INSERT@raw
SELECT1, 'Sample', 'FAIL' UNION ALL
SELECT2, 'trial', 'PASS' UNION ALL
SELECT3, 'Stride', 'Error' UNION ALL
SELECT4, 'Regression', 'PASS' UNION ALL
SELECT5, 'Regression', 'PASS' UNION ALL
SELECT6, 'Setup', 'Waived' UNION ALL
SELECT7, 'Setup', 'Defered' UNION ALL
SELECT8, 'Setup', 'Error' UNION ALL
SELECT9, 'Regression', 'Error' UNION ALL
SELECT10, 'Setup', 'Error' UNION ALL
SELECT11, 'Unit', 'Ready' UNION ALL
SELECT12, 'Unit', 'Ready' UNION ALL
SELECT13, 'Unit', 'Ready' UNION ALL
SELECT14, 'Unit', 'Ready'
Desired Output:
Feature Defered Error FAIL PASS Ready Waived
------- ------- ----- ---- ---- ----- ------
Regression 0.0 33.3 0.0 66.6 0.0 0.0
Sample 0.0 0.0 100.0 0.0 0.0 0.0
Setup 33.3 33.3 0.0 0.0 0.0 33.3
Stride 0.0 100.0 0.0 0.0 0.0 0.0
trial 0.0 0.0 0.0 100.0 0.0 0.0
Unit 0.0 0.0 0.0 0.0 100.0 0.0
The work that I have to date done on this problem is based on another topic in these forums. I cannot recall the name of the person on whose work this is based. If you recognize some hints of your work in what I Have done, please respond to this thread, and I'll give you all of the kudos that you deserve.
This is what I have so far:
SET NOCOUNT ON
---===== If the temp table to hold the results in exists, drop it
IF OBJECT_ID('TempDB..#Results') IS NOT NULL
DROP TABLE #Results
---===== Populate the temp table with results from your original query
SELECT TestListID, [Feature] AS AttributeName, [RunStatus] AS AttributeValue
INTO #Results
FROM @raw
IF @@ROWCOUNT = 0
RETURN
CREATE
INDEX [test] ON #Results ([AttributeName])
--===== Declare some local variables to hold some Dynamic SQL
DECLARE
@MySQL1 VARCHAR(8000)
DECLARE
@MySQL2 VARCHAR(8000)
DECLARE
@MySQL3 VARCHAR(8000)
--===== Build the SELECT clause
SET @MySQL1 = 'SELECT AttributeName AS Feature, '
--===== Build the select LIST
SELECT @MySQL2 = ISNULL(@MySQL2 + ',','') + '
(CASE WHEN AttributeName = ''' + d.AttributeName + '''
THEN 100.0 *
((SELECT COUNT(TestListID) FROM #Results WHERE ([AttributeValue]=''' + e.AttributeValue + ''' AND [AttributeName] = ''' + d.AttributeName + ''')) /
(SELECT COUNT(TestListID) FROM #Results WHERE ([AttributeName] = ''' + d.AttributeName + ''')))
ELSE 0 END) AS ' + '[' + e.AttributeValue + ']'
FROM
(SELECT DISTINCT TOP 100 PERCENT AttributeName FROM #Results ORDER BY AttributeName) d,
(SELECT DISTINCT TOP 100 PERCENT AttributeValue FROM #Results ORDER BY AttributeValue) e
--===== Build the FROM and GROUP BY clauses
SELECT @MySQL3 = CHAR(13) + 'FROM #Results GROUP BY AttributeName, AttributeValue'
--===== Display the resulting SQL
PRINT @MySQL1+@MySQL2+@MySQL3
--===== Execute the Dynamic SQL
EXEC ( @MySQL1 +@MySQL2 +@MySQL3 )
Any help or comments on this would be greatly appreciated.
Thanks,
Justin
November 13, 2007 at 1:46 pm
jones.justinw (11/13/2007)
The work that I have to date done on this problem is based on another topic in these forums. I cannot recall the name of the person on whose work this is based. If you recognize some hints of your work in what I Have done, please respond to this thread, and I'll give you all of the kudos that you deserve.
I am fairly certain that was Jeff Moden, and if I remember correctly the OP had some problem with computer attributes or CPUs or something like that. I have no idea where the post is, but I know I saw it here. Any how, if you do not need dynamically built column names the below should do what you want. I do not have an elegant way of truncating the extra 0's after the decimal, but maybe you do. Anyhow, I am sure Jeff will know what you are talking about, and will probably be able to point you to the post.
SELECT
Feature,
Defered = SUM(ROUND(CONVERT(DECIMAL(5,2),Defered)/CONVERT(DECIMAL(5,2),(Defered+Error+Fail+Pass+Ready+Waived)),2)),
Error = SUM(ROUND(CONVERT(DECIMAL(5,2),Error)/CONVERT(DECIMAL(5,2),(Defered+Error+Fail+Pass+Ready+Waived)),2)),
Fail = SUM(ROUND(CONVERT(DECIMAL(5,2),Fail)/CONVERT(DECIMAL(5,2),(Defered+Error+Fail+Pass+Ready+Waived)),2)),
Pass = SUM(ROUND(CONVERT(DECIMAL(5,2),Pass)/CONVERT(DECIMAL(5,2),(Defered+Error+Fail+Pass+Ready+Waived)),2)),
Ready = SUM(ROUND(CONVERT(DECIMAL(5,2),Ready)/CONVERT(DECIMAL(5,2),(Defered+Error+Fail+Pass+Ready+Waived)),2)),
Waived = SUM(ROUND(CONVERT(DECIMAL(5,2),Waived)/CONVERT(DECIMAL(5,2),(Defered+Error+Fail+Pass+Ready+Waived)),2))
FROM
(
SELECT
Feature,
Defered = SUM(CASE WHEN RunStatus = 'Defered' THEN 1 ELSE 0 END),
Error = SUM(CASE WHEN RunStatus = 'Error' THEN 1 ELSE 0 END),
Fail = SUM(CASE WHEN RunStatus = 'Fail' THEN 1 ELSE 0 END),
Pass = SUM(CASE WHEN RunStatus = 'Pass' THEN 1 ELSE 0 END),
Ready = SUM(CASE WHEN RunStatus = 'Ready' THEN 1 ELSE 0 END),
Waived = SUM(CASE WHEN RunStatus = 'Waived' THEN 1 ELSE 0 END)
FROM #Raw
GROUP BY Feature
)t1
GROUP BY Feature
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
November 13, 2007 at 1:50 pm
http://www.sqlservercentral.com/Forums/Topic242919-8-1.aspx
I think this is the post you are talking about
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
November 13, 2007 at 1:59 pm
Thanks Greg for your input. It was indeed work from Jeff Moden that I based my efforts off of. Thank You Jeff!
As far as my pivot problem goes, the "Feaure" and "RunStatus" values are constantly changing. They are never fixed. This is why I had to to go with the dynamic SQL approach.
-Justin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply