views:

37

answers:

1

I have a query that I cannot get to work right. I have 3 tables; Person, PersonProgram and Category.

Person: ID, ....    
PersonProgram: ID, PersonID, Category, Code ...    
Category: ID, ...

The Person table has 1 record for each person and the PersonProgram has multiple programs per person. There are 4 categories and I need to pull into a single row, of each person, with specific Program of each category.

Person Table:

1
2
3

PersonProgram Table

1, 1, 1, 1
2, 1, 2, 1
3, 1, 1, 3
4, 2, 1, 1
5, 2, 3, 3

What the desired outcome should be:

PersonID, ProgramIDforCat1, ProgramIDforCat2, ProgramIDforCat3, ProgramIDforCat4
1, 1, 2, NULL, NULL
2, 1, NULL, 3, NULL

The problem is that there is multiple Program records for each person and category with a code of 1, 2 or 3. I need to put priority on Code 1 then Code 3 and ignore the rest, while still only pulling 1 record, or NULL if it does not exist.

I am losing it trying to get this to work.

FYI, it has to be in a view.

Thanks for any help.

+1  A: 
WITH Person AS
(
SELECT 1 AS ID UNION ALL
SELECT 2 AS ID UNION ALL
SELECT 3 AS ID
),
PersonProgram AS
(
SELECT 1 AS ID, 1 AS PersonID, 1 AS Category, 1 AS Code UNION ALL
SELECT 2, 1, 2, 1 UNION ALL
SELECT 3, 1, 1, 3 UNION ALL
SELECT 4, 2, 1, 1 UNION ALL
SELECT 5, 2, 3, 3
),
pp2 AS
(
SELECT *
,ROW_NUMBER() OVER 
 (PARTITION BY PersonID, Category 
      ORDER BY CASE WHEN Code = 1 THEN 0 ELSE 1 END,
               CASE WHEN Code = 3 THEN 0 ELSE 1 END) AS RN
 FROM PersonProgram
)
select PersonID ,
max(case when Category =1 then pp2.ID end) ProgramIDforCat1,
max(case when Category =2 then pp2.ID end) ProgramIDforCat2,
max(case when Category =3 then pp2.ID end) ProgramIDforCat3,
max(case when Category =4 then pp2.ID end) ProgramIDforCat4
from Person p join pp2 
on pp2.PersonID = p.ID
WHERE RN=1
group by PersonID

Returns

PersonID    ProgramIDforCat1 ProgramIDforCat2 ProgramIDforCat3 ProgramIDforCat4
----------- ---------------- ---------------- ---------------- ----------------
1           1                2                NULL             NULL
2           4                NULL             5                NULL

This is different from your expected results. (though I can make it the same by using pp2.Category rather than pp2.ID) Can you clarify?

Martin Smith
The results in my example were probably wrong, WAY to long with this query. I will look at this, it looks VERY promising. Quitting time on Friday :)
Dustin Laine
Can `ROW_NUMBER` be used in a view? If I add it SSMS crashes everytime. Using SQL 2005.
Dustin Laine
@durilai - Yes. If the designer can't cope with it try doing it through script `CREATE VIEW dbo.ViewName AS WITH pp2 AS ...`
Martin Smith
This was the best way to go about this, the view worked fine, but the designer blew up. Don't care about that. Thanks, much more elegant than what I was attempting.
Dustin Laine