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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy