October 4, 2006 at 4:32 pm
For simplicity sake say I have a table like so:
u_id, job_id, m_hours, t_hours, w_hours, th_hours, f_hours,
and I wanted to get a result set like this:
1, 17, m_hours
1, 17, t_hours
...
1, 17, f_hours
1, 22, m_hours
1, 22, t_hours
...
1, 22, f_hours
2, 22, m_hours
....
x, yy, f_hours
Odd I know, but its what I need. The inital way I thought of doing this would be to inner join the table a couple of times, but that would work to expand the rows instead of having them appear multiple times.
Is there even a term used for something like this that I could do some google searching on?
Any pointers would be appreciated!
Cheers
October 5, 2006 at 3:25 am
If possible, normalize the table.
If you have to live with it, something LIKE this should work.
SELECT D.u_id, D.job_id, D.Hours
FROM (
SELECT u_id, job_id, m_hours as Hours, 1 as HourOrder FROM YourTable
UNION ALL
SELECT u_id, job_id, t_hours, 2 FROM YourTable
UNION ALL
SELECT u_id, job_id, w_hours, 3 FROM YourTable
UNION ALL
SELECT u_id, job_id, th_hours, 4 FROM YourTable
UNION ALL
SELECT u_id, job_id, f_hours, 5 FROM YourTable ) D
ORDER BY D.u_id, D.job_id, D.HourOrder
or
SELECT Y.u_id, Y.job_id
,CASE H.HourOrder
WHEN 1 THEN m_hours
WHEN 2 THEN t_hours
WHEN 3 THEN w_hours
WHEN 4 THEN th_hours
ELSE f_hours END as Hours
FROM YourTable Y
CROSS JOIN (
SELECT 1 as HourOrder UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 ) H
ORDER BY Y.u_id, Y.job_id, H.HourOrder
October 5, 2006 at 6:52 am
You Rock!
The first version of that worked as easy as can be! I was able to join that to the other tables and had my output in 2 minutes flat.
Normalizing the table isn't really an option. This query runs once a week for an export and the table is pretty small so I'm not too worried about speed (yet).
Thanks again!
October 5, 2006 at 6:57 am
FYI, the second version should be faster as it scans YourTable once instead of five times.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply