November 12, 2007 at 3:21 pm
Hi All,
I have a table that has columns 1)ProductName and 2)qualification.
The reporting scenario is i have to display the highest qualification among all qualifications for the particular product name:
Suppose 3 qualifications(B,P,D)
D__lowest
B__Medium
P__highest
So i need P always if P is there among the group.
The different conditions would be
1)If the ProductName has only only type of qualification then display that.
2)If both B and D, then display only B.
3)If both P and D, then display only p.
4) If p,B and D then display only P.
The current table would be like:
ProductName QualificationCol3Col4
P1B
P1P
P1D
P2B
P2D
P3P
P3D
P4D
P5B
P5B
P5B
The expected Result:
ProductName QualificationCol3Col4
P1P
P2B
P3P
P4D
P5B
Any sort of help would be great.
Alicia Rose
November 12, 2007 at 3:44 pm
There may be some fancier way to do this in 2005 (or just some fancier way in general) but this should get you going in the right direction.
DECLARE @table TABLE (ProductName char(2), qualification char(1))
INSERT INTO @Table
SELECT 'P1', 'B' UNION ALL
SELECT 'P1', 'P' UNION ALL
SELECT 'P1', 'D' UNION ALL
SELECT 'P2', 'B' UNION ALL
SELECT 'P2', 'D' UNION ALL
SELECT 'P3', 'P' UNION ALL
SELECT 'P3', 'D' UNION ALL
SELECT 'P4', 'D' UNION ALL
SELECT 'P5', 'B' UNION ALL
SELECT 'P5', 'B' UNION ALL
SELECT 'P5', 'B'
SELECT ProductName,
CASE MIN(CASE Qualification
WHEN 'P' THEN 1
WHEN 'B' THEN 2
WHEN 'D' THEN 3
END)
WHEN 1 THEN 'P'
WHEN 2 THEN 'B'
WHEN 3 THEN 'D'
END AS Qualificaiton
FROM @Table
GROUP BY ProductName
November 12, 2007 at 4:27 pm
Hi John,
Thanks a lot for the script.
That onez pretty simple and fast too.:)
Works great!!!!
Alicia Rose
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply