Need help with Pivot query or other mechanism to report below data

• dgendera@gmail.com

  Valued Member

  Points: 64

  More actions

  January 22, 2024 at 11:04 am

  #4365735

  Hi, I've got below dataset (this is sample dataset)

  

  I would like to show dataset following way

  <table style="height: 212px;" width="758">

  <tbody>

  <tr>

  <td width="154"></td>

  <td width="64">srv01</td>

  <td width="64">srv02</td>

  <td width="64">srv03</td>

  <td width="64">srv04</td>

  <td width="64">srv05</td>

  </tr>

  <tr>

  <td>2024-01-22 10:46:00.000</td>

  <td>10</td>

  <td>20</td>

  <td>30</td>

  <td>40</td>

  <td>50</td>

  </tr>

  <tr>

  <td>2024-01-22 10:47:00.000</td>

  <td>10</td>

  <td>20</td>

  <td>30</td>

  <td>40</td>

  <td>50</td>

  </tr>

  <tr>

  <td>2024-01-22 10:48:00.000</td>

  <td>10</td>

  <td>20</td>

  <td>30</td>

  <td>40</td>

  <td>50

  </td>

  </tr>

  </tbody>

  </table>

  Apology for bad formatting I hope you get the idea.

  Here is the DDL to create table and insert some sample data, any help would be much appreciated

  Create Temp table

  CREATE TABLE #SrvInfo
  (
  logdate datetime,
  srvname nvarchar(20),
  queueinfo int
  );

  Sample data

  insert into #SrvInfo (logdate,srvname,queueinfo) values (format(getutcdate(),'yyyy-MM-dd HH:mm'), 'srv01', 10)
  insert into #SrvInfo (logdate,srvname,queueinfo) values (format(getutcdate(),'yyyy-MM-dd HH:mm'), 'srv02', 20)
  insert into #SrvInfo (logdate,srvname,queueinfo) values (format(getutcdate(),'yyyy-MM-dd HH:mm'), 'srv03', 30)
  insert into #SrvInfo (logdate,srvname,queueinfo) values (format(getutcdate(),'yyyy-MM-dd HH:mm'), 'srv04', 40)
  insert into #SrvInfo (logdate,srvname,queueinfo) values (format(getutcdate(),'yyyy-MM-dd HH:mm'), 'srv05', 50)
  insert into #SrvInfo (logdate,srvname,queueinfo) values (format(dateadd(MINUTE,1,getutcdate()),'yyyy-MM-dd HH:mm'), 'srv01', 10)
  insert into #SrvInfo (logdate,srvname,queueinfo) values (format(dateadd(MINUTE,1,getutcdate()),'yyyy-MM-dd HH:mm'), 'srv02', 20)
  insert into #SrvInfo (logdate,srvname,queueinfo) values (format(dateadd(MINUTE,1,getutcdate()),'yyyy-MM-dd HH:mm'), 'srv03', 30)
  insert into #SrvInfo (logdate,srvname,queueinfo) values (format(dateadd(MINUTE,1,getutcdate()),'yyyy-MM-dd HH:mm'), 'srv04', 40)
  insert into #SrvInfo (logdate,srvname,queueinfo) values (format(dateadd(MINUTE,1,getutcdate()),'yyyy-MM-dd HH:mm'), 'srv05', 50)

• dgendera@gmail.com

  Valued Member

  Points: 64

  More actions

  January 22, 2024 at 11:06 am

  #4365738

  I see that table formatting did not go correct below image of the result

  

  Thx again for all the help

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  January 22, 2024 at 11:56 am

  #4365748

  Something like this, perhaps?

  SELECT si.logdate
        ,srv01 = MAX (IIF(si.srvname = 'srv01', si.queueinfo, NULL))
        ,srv02 = MAX (IIF(si.srvname = 'srv02', si.queueinfo, NULL))
        ,srv03 = MAX (IIF(si.srvname = 'srv03', si.queueinfo, NULL))
        ,srv04 = MAX (IIF(si.srvname = 'srv04', si.queueinfo, NULL))
        ,srv05 = MAX (IIF(si.srvname = 'srv05', si.queueinfo, NULL))
  FROM #SrvInfo si
  GROUP BY si.logdate;

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• dgendera@gmail.com

  Valued Member

  Points: 64

  More actions

  January 22, 2024 at 1:31 pm

  #4365790

  Phil, Thx this is exactly what I was looking for, appreciate the quick response and help in this matter,

  Have a nice day

  Rgds

  Denis