Good afternoon (but just barely for me),
I'm encountering a problem that I have previously, data that has been warehoused and thus denormalized but needs to be normailized for a query I want to run. This seems pretty common in healthcare warehouses, I don't know if it exists as prominently in other types of warehousing systems. Below is an example:

Data as it may have existed previously
PatientID..Diagnosis....Treatment
1............Headache....Sent Home
2............Broken Arm..Cast
1............Migraine......Medicated
1............Unconscious.Revived

Data as it is warehouse
PID.Diag1.Diag2.Diag3.Treat1.Treat2.Treat3
1....Hach..Migr..Unc...Home...Med....Rev
2....BArm.................Cast...................

If I want to retrieve patients with diagnoses or treatments or a value list of either from the warehouse I typically do it with UNION statements, something like:
SELECT Diag1 FROM warehouseTable
UNION
SELECT Diag2 FROM warehouseTable
UNION
SELECT Diag3 FROM warehouseTable

Today I've had some free time so I've been looking at ways of renormalizingthe data from the warehouse or generating a value list that would be more efficient, intelligent and reusable. In my head it would go something like

columnNameRoot AS Diag
FOR Num=1; Num<=3; Num++
SELECT columnNameRootNum FROM warehouseTable
LOOP

Now I realize I'm mixing all manner of code that doesn't belong together there but it should make roughly logical sense. The idea is that I could just change the variable name.

This seems like something that a lot of people have probably come up against and come up with interesting and cunning solutions to. I was hoping to find some kind of standard solution that was better than my UNION solution.

I didn't place this under a specific database topic because it seems like it could happen in nearly any database system (I've seen it in at least 3 different ones)

Thanks for taking the time to look at this

Run your query against the DW staging tables. The normalized data should already exist there.

-PatP

to my knowledge, I've not had access to the staging areas of any datawarehouse I have queries against. I would think that would be very atypical, giving warehouse users staging access.

Search Jeff Smith's blog for some tips on normalising denormalised data. Also your RDBMS is significant - I think UNPIVOT is an option in SQL Server.

Thanks for the refernce Pootle I will take a look to see what I can find.

They're calling that a data warehouse? I think I just vomited a bit in my mouth.