How to change output format

• rw_ebox

  SSC Eights!

  Points: 803

  More actions

  January 7, 2009 at 3:56 pm

  #155101

  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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 7, 2009 at 4:06 pm

  #922984

  Read about PIVOT in Books Online, and start with that. If you still have questions, let us know.

• Dugi

  SSCoach

  Points: 17998

  More actions

  January 7, 2009 at 4:39 pm

  #922996

  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:

  ============================================================
  SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
  http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

• rw_ebox

  SSC Eights!

  Points: 803

  More actions

  January 8, 2009 at 11:55 am

  #923604

  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)