July 19, 2004 at 3:39 pm
Hello All:
I am using the following stored procedure below to create the equivalent of a crosstab query. However, I would like to suppress any rows that have zeroes across each field.
for example
A U X
0 0 0 should be eliminated
0 0 1 should stay
CREATE PROCEDURE crossy (
@TestPlan varchar(100)
)
AS
select strBusinessFunction,
SUM(CASE WHEN strCondItionStatus ='A'and strTestPlan = @TestPlan THEN 1 ELSE 0 END)AS 'A',
SUM(CASE WHEN strCondItionStatus ='U'and strTestPlan = @TestPlan THEN 1 ELSE 0 END)AS 'U',
SUM(CASE WHEN strCondItionStatus ='X'and strTestPlan = @TestPlan THEN 1 ELSE 0 END)AS 'X'
FROM tblconditions
GROUP BY strBusinessFunction
ORDER BY strBusinessFunction
GO
July 19, 2004 at 4:06 pm
hey joe,
you are almost there ... to get rid of rows with any zeroes your query would have to be a subquery ...
so, the final SQL would look something like this:
SELECT *
FROM
(
SELECT
strBusinessFunction,
A = SUM(CASE WHEN strCondItionStatus ='A'and strTestPlan = @TestPlan THEN 1 ELSE 0 END),
U = SUM(CASE WHEN strCondItionStatus ='U'and strTestPlan = @TestPlan THEN 1 ELSE 0 END),
X = SUM(CASE WHEN strCondItionStatus ='X'and strTestPlan = @TestPlan THEN 1 ELSE 0 END)'
FROM
tblconditions
GROUP BY strBusinessFunction
ORDER BY strBusinessFunction
) p
WHERE
0 != (p.A + p.U + p.X)
JP
July 20, 2004 at 7:04 am
Does anyone have an SP that will do this for any table, with any structure? Something that would take a table name and build a statement based on the metadata and generate the sql and execute it? It'd be pretty handy, I just don't have time to build it.
July 20, 2004 at 2:04 pm
select strBusinessFunction,
SUM(CASE WHEN strCondItionStatus ='A'and strTestPlan = @TestPlan THEN 1 ELSE 0 END)AS 'A',
SUM(CASE WHEN strCondItionStatus ='U'and strTestPlan = @TestPlan THEN 1 ELSE 0 END)AS 'U',
SUM(CASE WHEN strCondItionStatus ='X'and strTestPlan = @TestPlan THEN 1 ELSE 0 END)AS 'X'
FROM tblconditions
-- insertText
WHERE strCondItionStatus in ('A', 'U', 'X')
-- end insertText
GROUP BY strBusinessFunction
ORDER BY strBusinessFunction
Just adding a WHERE clause I think will be more performant, and I think this get the same result.
Chris Hofland
August 19, 2004 at 1:08 pm
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply