| CID | DATE_REPORTED | MED_CODE | MEDICATION | REPORTING_PROVIDER | SOURCE | MEDICATION_TYPE |
| 77518254 | 14.10.2009 | 0032 | ARTANE(TRIHEXYPHENIDYL)(ANTI-CHOLINERGICS) | 20 | MICP | MEDICATION 1 |
| 77518254 | 14.10.2009 | 0008 | LUVOX(FLUVOXAMINE)(ANTI-DEPRESSANTS) | 20 | MICP | MEDICATION 3 |
| 77518254 | 14.10.2009 | 0101 | MARPLAN(ISOCARBOXAZID)(ANTI-DEPRESSANTS) | 20 | MICP | MEDICATION 3 |
| 77518254 | 14.10.2009 | 0112 | MARPLAN(ISOCARBOXAZID)(ANTI-DEPRESSANTS) | 20 | MICP | MEDICATION 4 |
Reverse crosstab query in Oracle
Most of us are familiar with cross-tabbing in queries. It's basically changing rows into columns.
If you have a requirement to do the reverse, do the following:
Let’s say this is the initial table structure:
Let's say you need to change this into the following structure: (Columns into Rows)
Here is the sql for reverse cross tabbing:
This would be the result:
Subscribe to:
Post Comments (Atom)
1 comments:
Your example was perfect for what I was looking for. It took me a couple hours to figure out how to apply it to my situation. What I realize now is that the from select statement on the rownum is making a cartesian join. The resultant recordset will have a row for each of the rownums. Then, the decodes determine which value is returned dependent on the rownum.
I have a table with manufacturing average costs per part number. So essentially the structure was part, mat, moh, lab, loh, odc. I needed to transpose that for a data migration to part,type,cost where type is; 1=mat, 2=moh, 3=lab, 4=loh, 5=odc.
SELECT t1.part, CASE
WHEN r = 1 THEN '1'
WHEN r = 2 THEN '2'
WHEN r = 3 THEN '3'
WHEN r = 4 THEN '4'
WHEN r = 5 THEN '5'
END
AS cost_element,
CASE
WHEN r = 1 THEN t1.mat
WHEN r = 2 THEN t1.moh
WHEN r = 3 THEN t1.lab
WHEN r = 4 THEN t1.odc
WHEN r = 5 THEN t1.loh
END
AS cost
FROM item_avg_cost t1,
( SELECT ROWNUM R
FROM ALL_OBJECTS
WHERE ROWNUM <= 5);
I can't thank you enough for posting your example. -phil
Post a Comment