January 7, 2009 at 3:56 pm
Hi Folks,
I have a table which has the following data.
DateLunValue
1/6/2009 22:42Lun17
1/6/2009 22:42Lun212
1/6/2009 22:42Lun310
1/6/2009 22:42Lun48
1/6/2009 22:42Lun67
1/6/2009 22:42Lun74
1/6/2009 22:42Lun82
1/6/2009 22:43Lun18
1/6/2009 22:43Lun213
1/6/2009 22:43Lun39
1/6/2009 22:43Lun47
1/6/2009 22:43Lun66
1/6/2009 22:43Lun73
1/6/2009 22:43Lun83
1/6/2009 22:44Lun17
1/6/2009 22:44Lun212
1/6/2009 22:44Lun310
1/6/2009 22:44Lun48
1/6/2009 22:44Lun67
1/6/2009 22:44Lun74
1/6/2009 22:44Lun82
Could you please tell me how to create a SQL query and let the output like the output below?
DateLun1Lun2Lun3Lun4Lun6Lun7Lun8
1/6/2009 22:42712108742
1/6/2009 22:4381397633
1/6/2009 22:44712108742
Thanks,
Ray Wang
January 7, 2009 at 4:06 pm
Read about PIVOT in Books Online, and start with that. If you still have questions, let us know.
January 7, 2009 at 4:39 pm
OR
Try something like this:
SELECT DATE
SUM(CASE WHEN LUN = 'LUN1' THEN VALUE ELSE 0 END) AS LUN1,
SUM(CASE WHEN LUN = 'LUN2' THEN VALUE ELSE 0 END) AS LUN2,
.
. ( OTHER LUN VALUES)
.
FROM YOUR_TABLE
GROUP BY DATE
:hehe:
January 8, 2009 at 11:55 am
Thank you, both.
I learned something and appreciate your hekp.
The following is my SQL query.
SELECT CREATE_DATE,
SUM(CASE WHEN LUN_NAME = 'LUN1' THEN LUN_VALUE ELSE 0 END) AS LUN1,
SUM(CASE WHEN LUN_NAME = 'LUN2' THEN LUN_VALUE ELSE 0 END) AS LUN2,
SUM(CASE WHEN LUN_NAME = 'LUN3' THEN LUN_VALUE ELSE 0 END) AS LUN3,
SUM(CASE WHEN LUN_NAME = 'LUN4' THEN LUN_VALUE ELSE 0 END) AS LUN4
FROM STORAGE_VALUE
GROUP BY CREATE_DATE
The output is,
CREATE_DATE LUN1 LUN2 LUN3 LUN4
----------------------- ---------------------- ---------------------- ---------------------- ----------------------
2009-01-06 22:42:00.000 7 12 10 8
2009-01-06 22:43:00.000 8 13 9 7
2009-01-06 22:44:00.000 7 12 10 8
(3 row(s) affected)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply