November 11, 2013 at 11:07 pm
I have the following table:
FormKey Flag1 Flag2 Flag3
---------- ------ ------ ------
1 130 132 129
1 130 130 130
1 130 NULL NULL
2 132 NULL NULL
2 129 130 NULL
2 NULL 129 NULL
I would like to write a query/tsql script to get the following result:
(No cursor, no complicated loops and no multiple record creation of the same record, this table is huge like to make it as fast as possible)
FormKey Flag1 Flag2 Flag3
---------- ------ ------ ------
1 130 132 135
2 132 135 NULL
1. The Group By key is FormKey.
2. If the column has at least one record value as 132 then the final or rollup value should be 132 (all other value should be ignored).
3. If the column has all the values are 130 then the final or rollup value should be 130.
4. If the column has all the values are NULL then the final or rollup value should be NULL.
5. If the column has the values like 130,129, NULL and other than 132 the final or rollup value should be 135.
I need this ASAP.
Thanks in Advance for all your help and review.:-)
November 12, 2013 at 2:21 am
Odd problem. If you didn't say "this table is huge" to throw me off the scent, I'd say it looks like homework.
WITH SampleData (FormKey,Flag1,Flag2,Flag3) AS
(
SELECT 1,130,132,129
UNION ALL SELECT 1,130,130,130
UNION ALL SELECT 1,130,NULL,NULL
UNION ALL SELECT 2,132,NULL,NULL
UNION ALL SELECT 2,129,130,NULL
UNION ALL SELECT 2,NULL,129,NULL
)
SELECT FormKey
,Flag1=CASE
WHEN COUNT(CASE WHEN Flag1 = 132 THEN Flag1 END) >= 1 THEN 132
WHEN COUNT(CASE WHEN Flag1 = 130 THEN Flag1 END) = COUNT(*) THEN 130
WHEN COUNT(CASE WHEN Flag1 IS NULL THEN 1 END) = COUNT(*) THEN NULL
WHEN COUNT(CASE WHEN Flag1 = 132 THEN Flag1 END) = 0 THEN 135
END
,Flag2=CASE
WHEN COUNT(CASE WHEN Flag2 = 132 THEN Flag2 END) >= 1 THEN 132
WHEN COUNT(CASE WHEN Flag2 = 130 THEN Flag2 END) = COUNT(*) THEN 130
WHEN COUNT(CASE WHEN Flag2 IS NULL THEN 1 END) = COUNT(*) THEN NULL
WHEN COUNT(CASE WHEN Flag2 = 132 THEN Flag2 END) = 0 THEN 135
END
,Flag3=CASE
WHEN COUNT(CASE WHEN Flag3 = 132 THEN Flag3 END) >= 1 THEN 132
WHEN COUNT(CASE WHEN Flag3 = 130 THEN Flag3 END) = COUNT(*) THEN 130
WHEN COUNT(CASE WHEN Flag3 IS NULL THEN 1 END) = COUNT(*) THEN NULL
WHEN COUNT(CASE WHEN Flag3 = 132 THEN Flag3 END) = 0 THEN 135
END
FROM SampleData
GROUP BY FormKey;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 12, 2013 at 11:15 am
Try:
SELECT
FormKey,
CASE
WHEN MIN(Flag1) = 129 AND MAX(Flag1) = 130 AND COUNT(Flag1) = 2 THEN 135
ELSE MAX(Flag1)
END AS Flag1,
CASE
WHEN MIN(Flag2) = 129 AND MAX(Flag2) = 130 AND COUNT(Flag2) = 2 THEN 135
ELSE MAX(Flag2)
END AS Flag2,
CASE
WHEN MIN(Flag3) = 129 AND MAX(Flag3) = 130 AND COUNT(Flag3) = 2 THEN 135
ELSE MAX(Flag3)
END AS Flag3
FROM
SampleData
GROUP BY
FormKey;
P.S. Thanks to Dwain for the sample data.
November 12, 2013 at 11:46 am
Hi Dwain and hunchback.
Thank you very much for help, it is working as expected.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply