Pivot - what is wrong with this query?

• Marek Grzymala

  SSCommitted

  Points: 1946

  More actions

  March 21, 2017 at 5:35 am

  #336165

  Hello All, I need a second pair of eyes to find what I am doing wrong here and why doesn;t it work:

  IF OBJECT_ID('Tempdb..#SourceTable') IS NOT NULL DROP TABLE #SourceTable
CREATE TABLE #SourceTable ([VMID] INT, [VM_NAME] VARCHAR(100), [FIELD_NAME] VARCHAR(100), [FIELD_VALUE] VARCHAR(100))

  INSERT INTO #SourceTable ([VMID], [VM_NAME], [FIELD_NAME], [FIELD_VALUE])
VALUES
    ('57499', 'machine_1', 'BusinessCriticality', '3'),
    ('57499', 'machine_1', 'CostCenter', '912'),
    ('57499', 'machine_1', 'Last Backup', '24.03.2015 21:03:57'),

      ('116007', 'machine_2', 'BusinessCriticality', '3'),
    ('116007', 'machine_2', 'CostCenter', ''),
    ('116007', 'machine_2', 'Last Backup', '20.03.2017 21:05:41'),

      ('129708', 'machine_3', 'BusinessCriticality', '3'),
    ('129708', 'machine_3', 'CostCenter', '911'),
    ('129708', 'machine_3', 'Last Backup', '3/20/2017 10:38:10 PM')

  SELECT [VM_NAME], [BusinessCriticality], [CostCenter], [Last Backup]
FROM
    (
        SELECT * FROM #SourceTable
    ) S
PIVOT
    (
        AVG([VMID])
        FOR FIELD_NAME IN ([BusinessCriticality], [CostCenter], [Last Backup])
    ) AS PivotTable
ORDER BY [VM_NAME]

• DesNorton

  SSC-Insane

  Points: 24424

  More actions

  March 21, 2017 at 5:40 am

  #1934129

  The query works.

  What is your expected outcome?

  ---

  How to post data/code on a forum to get the best help.

• Phil Parkin

  SSC Guru

  Points: 246993

  More actions

  March 21, 2017 at 5:52 am

  #1934130

  This, maybe?

  IF OBJECT_ID('Tempdb..#SourceTable') IS NOT NULL DROP TABLE #SourceTable
CREATE TABLE #SourceTable ([VMID] INT, [VM_NAME] VARCHAR(100), [FIELD_NAME] VARCHAR(100), [FIELD_VALUE] VARCHAR(100))

  INSERT INTO #SourceTable ([VMID], [VM_NAME], [FIELD_NAME], [FIELD_VALUE])
VALUES
  ('57499', 'machine_1', 'BusinessCriticality', '3'),
  ('57499', 'machine_1', 'CostCenter', '912'),
  ('57499', 'machine_1', 'Last Backup', '24.03.2015 21:03:57'),

    ('116007', 'machine_2', 'BusinessCriticality', '3'),
  ('116007', 'machine_2', 'CostCenter', ''),
  ('116007', 'machine_2', 'Last Backup', '20.03.2017 21:05:41'),

    ('129708', 'machine_3', 'BusinessCriticality', '3'),
  ('129708', 'machine_3', 'CostCenter', '911'),
  ('129708', 'machine_3', 'Last Backup', '3/20/2017 10:38:10 PM')

  SELECT [VM_NAME], [BusinessCriticality], [CostCenter], [Last Backup]
FROM
  (
   SELECT * FROM #SourceTable
  ) S
PIVOT
  (
   Max([FIELD_VALUE])
   FOR FIELD_NAME IN ([BusinessCriticality], [CostCenter], [Last Backup])
  ) AS PivotTable
ORDER BY [VM_NAME]

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Marek Grzymala

  SSCommitted

  Points: 1946

  More actions

  March 21, 2017 at 5:57 am

  #1934131

  Expected outcome should be:

  VM_NAME	BusinessCriticality	CostCenter	Last Backup
  machine_1	3	921	24.03.2015 21:03:57
  machine_2	3		20.03.2017 21:05:41
  machine_3	3	911	3/20/2017 22:38

• Marek Grzymala

  SSCommitted

  Points: 1946

  More actions

  March 21, 2017 at 5:59 am

  #1934132

  Phil Parkin - Tuesday, March 21, 2017 5:52 AM

  This, maybe?

  IF OBJECT_ID('Tempdb..#SourceTable') IS NOT NULL DROP TABLE #SourceTable
CREATE TABLE #SourceTable ([VMID] INT, [VM_NAME] VARCHAR(100), [FIELD_NAME] VARCHAR(100), [FIELD_VALUE] VARCHAR(100))

  INSERT INTO #SourceTable ([VMID], [VM_NAME], [FIELD_NAME], [FIELD_VALUE])
VALUES
  ('57499', 'machine_1', 'BusinessCriticality', '3'),
  ('57499', 'machine_1', 'CostCenter', '912'),
  ('57499', 'machine_1', 'Last Backup', '24.03.2015 21:03:57'),

    ('116007', 'machine_2', 'BusinessCriticality', '3'),
  ('116007', 'machine_2', 'CostCenter', ''),
  ('116007', 'machine_2', 'Last Backup', '20.03.2017 21:05:41'),

    ('129708', 'machine_3', 'BusinessCriticality', '3'),
  ('129708', 'machine_3', 'CostCenter', '911'),
  ('129708', 'machine_3', 'Last Backup', '3/20/2017 10:38:10 PM')

  SELECT [VM_NAME], [BusinessCriticality], [CostCenter], [Last Backup]
FROM
  (
   SELECT * FROM #SourceTable
  ) S
PIVOT
  (
   Max([FIELD_VALUE])
   FOR FIELD_NAME IN ([BusinessCriticality], [CostCenter], [Last Backup])
  ) AS PivotTable
ORDER BY [VM_NAME]

  Yes, that's exactly what I wanted, Thank You!