Query - Heavy Denormilozation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query - Heavy Denormilozation

Author

Topic

LWatts
Starting Member

2 Posts

Posted - 2008-04-01 : 14:08:57

I'm needing a query that can select from multiple rows the same column as separate columns based on a another column's ID.

Simplified ERD:

Sample dataset:

Pysdo SQL:
SELECT DISTINCT FlagOccurance.PersonID, FlagOccurance.FlagValue AS Flag1, FlagOccurance.FlagValue AS Flag2
FROM FlagOccurance INNER JOIN
Flag ON FlagOccurance.FlagID = Flag.FlagID
Where (the FlagOccurance.FlagID of Flag1 = Flag.FlagID)
and (the FlagOccurance.FlagID of Flag2 = Flag.FlagID)

Is there a name for what I'm trying to do? Could someone write a sample statement for this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-01 : 14:17:27

[code]SELECT p.PersonID,
MAX(CASE WHEN f.FlagName='Flag1' THEN fo.FlagValue END) AS Flag1,
MAX(CASE WHEN f.FlagName='Flag2' THEN fo.FlagValue END) AS Flag2,
MAX(CASE WHEN f.FlagName='Flag3' THEN fo.FlagValue END) AS Flag3,
MAX(CASE WHEN f.FlagName='Flag4' THEN fo.FlagValue END) AS Flag4,
MAX(CASE WHEN f.FlagName='Flag5' THEN fo.FlagValue END) AS Flag5,
.....
FROM Person p
INNER JOIN FlagOccurance fo
ON fo.PersonID=p.PersonID
INNER JOIN Flag f
ON f.FlagID=fo.FlagID
GROUP BY p.PersonID[/code]

LWatts
Starting Member

2 Posts

Posted - 2008-04-01 : 15:01:47

My thanks visakh16. You have ended eight hours of no progress on my part. That is exactly what I needed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources