Convert Coloumns to rows
Hi all,
I've got a table with the following data: (sample data)
Tbl_MachineHrs| ASSETID | DATE | HR23 | OPR23 | HR06 | OPR06 | HR07 | OPR07 | HR14 | OPR14 | HR15 | OPR15 | HR18 | OPR18 | HR19 | OPR19 | HR22 | OPR22 |
|---|
| 806CD03 | 11/07/2005 | 6817.9 | 116 | 6824.3 | 116 | 6824.9 | 103 | 6827.2 | 103 | 6827.6 | 46 |
|
|
|
| 6831.1 | 46 |
| 8066584 | 11/07/2005 | 5211.3 | 92 | 5218.8 | 92 | 5219.1 | 188 | 5223.8 | 188 | 5224.3 | 107 |
|
|
|
| 5227.7 | 107 |
| 8066554 | 11/07/2005 | 4056 | 8 | 4061.7 | 8 | 4062.6 | 45 | 4066.3 | 45 | 4066.3 |
|
|
|
|
| 4066.3 |
|
| 8066545 | 11/07/2005 | 8277 | 17 | 8282 | 17 | 8282 | 36 | 8288 | 36 | 8288 | 62 |
|
|
|
| 8294 | 62 |
| 8066544 | 11/07/2005 | 8297 | 10 | 8302 | 10 | 8303 | 106 | 8309 | 106 | 8309 | 113 |
|
|
|
| 8315 | 113 |
| 8066543 | 11/07/2005 | 9569.6 | 83 | 9575.8 | 83 | 9576 | 119 | 9581.7 | 119 | 9582 | 22 |
|
|
|
| 9584.7 | 22 |
The Coloumns represent hours. Eg Hr23 = 23:00, Hr07 = 07:00 (7 AM) Machine hours gets captured at 11pm, 7am, 2pm, 3pm, etc.
OPR = employee / operator number,
How can I write a query where this data can be shown in rows? eg:
ASSETID DATETIME OPR Hrs
8066543 11/07/2005 07:00:00 83 9576
8066543 11/07/2005 14:00:00 10 9582
8066543 11/07/2005 15:00:00 10 9593
806SP801 11/07/2005 23:00:00 99 10503
etc etc. How can I maybe cast or case or summin that to get that results?
Thanks alot in advance
Rudi
[13071 byte] By [
Tjoppie] at [2008-2-11]
Hi
With the enhanced features available in SQL Server 2005. There is no need to use SELECT CASE statements. You can use PIVOT and UNPIVOT for these cases. The following is the syntax:
-- To display in the normal as your out put
SELECT * FROM Tbl_MachineHrs
-- use this to see out as per your wish
SELECT ASSETID,DATE, HRS
FROM
(SELECT ASSETID,DATE,HR23, HR06, HR07 ,HR14 FROM Tbl_MachineHrs) p
UNPIVOT
(HRS FOR Tbl_MachineHrs IN (HR23, HR06, HR07, HR14) )AS unpvt
GO
Output
ASSETID DATE HRS
806CD03 2005-11-07 00:00:00.000 6817.9000
806CD03 2005-11-07 00:00:00.000 6824.3000
806CD03 2005-11-07 00:00:00.000 6824.9000
806CD03 2005-11-07 00:00:00.000 6827.2000
8066584 2005-11-07 00:00:00.000 5211.3000
8066584 2005-11-07 00:00:00.000 5218.8000
8066584 2005-11-07 00:00:00.000 5219.1000
8066584 2005-11-07 00:00:00.000 5223.8000
You can refer to books online for complete information on PIVOT and UNPIVOT.
You can use a query like below:
select m.ASSETID,
DATEADD(hour, CASE c.col
WHEN 1 THEN 11
WHEN 2 THEN 6
END, m.DATE) AS M_DATETIME,
CASE c.col
WHEN 1 THEN m.HR23
WHEN 2 THEN m.HR06
END AS Hrs,
CASE c.col
WHEN 1 THEN m.OPR23
WHEN 2 THEN m.OPR06
END AS Opr
from MachineHrs as m
cross join (
select 1 union all select 2
-- add as many numbers as you have columns to unpivot
) as c(col) ;Add more expressions in the CASE for the other columns and similarly in the derived table. This can also be done via multiple SELECT statements and UNION ALL but this is a good trick to use.
Note that SQL Server 2005 does have UNPIVOT operator but that is restricted to only one set of columns. So you could unpivot all the HR columns for example but it is kind of complicated to use for your result transformation.
