Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2013
    Posts
    1

    Unanswered: How to output the data to different column

    l am fresh in SQL, can somebody please help

    select hierarchy.hiername,devicefail.deviceid
    ,sum(DATEDIFF(minute,started,ended)) as duration
    ,100 - SUM(datediff(minute,started,ended))/(672 * 60.0000)*100 AS Uptime from devicefail
    LEFT JOIN device ON device.deviceid = devicefail.deviceid
    LEFT JOIN hierarchy ON device.hierlevel = hierarchy.hierlevel
    where devicefail.started >= '2013-02-01 00:00:00'and
    devicefail.ended <='2013-02-28 23:59:59'
    and devicefail.componentid like 201 or devicefail.componentid like 0
    group by devicefail.deviceid,hierarchy.hiername,devicefail. componentid
    order by hiername

    the above output as:
    hiername deviceid duration Uptime
    Airp 9541 20925 48.10267857
    Airp 9541 18919 53.07787699
    Airp 9542 1872 95.35714286
    Airp 9542 1552 96.15079365

    How could I achieve the result as following:
    hiername deviceid duration1 duration2 Uptime1 Uptime2
    Airp 9541 20925 18919 48.10267857 53.07787699
    Airp 9542 1872 1552 95.35714286 96.15079365

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That's really something you should do in your presentation layer (front-end, report, etc).

    What would happen when you get a hiername of "Airp 9543" - how would your results look?
    What would happen if your query returned 3 results for "Airp 9541" - how would your results look?


    i.e. do you have a finite defined limit to the number of columns you wish to output?
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •