Converting Column values into row along with Aggregate function

• torrid.inundate

  Old Hand

  Points: 367

  More actions

  March 13, 2017 at 1:14 pm

  #322604

  Hi Team,

  I need your help on my report.

  Scripts

  CREATE TABLE [dbo].[Sampletable](
     [VBLOCK] [nvarchar](50) NULL,
       [VM]  [nvarchar](50) NOT NULL,
       [CreatedDate] [date] NOT NULL,
     [CPU] [smallint] NULL
  )
  GO

  INSERT [dbo].[Sampletable] ([VBLOCK], [VM], [CreatedDate], [CPU]) VALUES (N'VBlock001', 'vm001', '01-SEP-2016',15)
  INSERT [dbo].[Sampletable] ([VBLOCK], [VM], [CreatedDate], [CPU]) VALUES (N'VBlock001', 'vm001', '02-SEP-2016',45)
  INSERT [dbo].[Sampletable] ([VBLOCK], [VM], [CreatedDate], [CPU]) VALUES (N'VBlock001', 'vm001', '03-SEP-2016',6)
  INSERT [dbo].[Sampletable] ([VBLOCK], [VM], [CreatedDate], [CPU]) VALUES (N'VBlock001', 'vm001', '04-SEP-2016',55)
  INSERT [dbo].[Sampletable] ([VBLOCK], [VM], [CreatedDate], [CPU]) VALUES (N'VBlock001', 'vm001', '05-SEP-2016',25)

  INSERT [dbo].[Sampletable] ([VBLOCK], [VM], [CreatedDate], [CPU]) VALUES (N'VBlock002', 'vm002', '01-SEP-2016',2)
  INSERT [dbo].[Sampletable] ([VBLOCK], [VM], [CreatedDate], [CPU]) VALUES (N'VBlock002', 'vm002', '02-SEP-2016',15)
  INSERT [dbo].[Sampletable] ([VBLOCK], [VM], [CreatedDate], [CPU]) VALUES (N'VBlock002', 'vm002', '03-SEP-2016',20)
  INSERT [dbo].[Sampletable] ([VBLOCK], [VM], [CreatedDate], [CPU]) VALUES (N'VBlock002', 'vm002', '04-SEP-2016',25)

  GO
  SELECT * FROM [dbo].[Sampletable] 
  GO

  VBLOCK        VM     CreatedDate    CPU
  VBlock001    vm001    2016-09-01    15
  VBlock001    vm001    2016-09-02    45
  VBlock001    vm001    2016-09-03    6
  VBlock001    vm001    2016-09-04    55
  VBlock001    vm001    2016-09-05    25
  VBlock002    vm002    2016-09-01    2
  VBlock002    vm002    2016-09-02    15
  VBlock002    vm002    2016-09-03    20
  VBlock002    vm002    2016-09-04    25

  
  I want o/p like this ( aggregate function with column to row conversion)

  Vblock         VM         CPU Average    CPU Max        Day 1    Day 2    Day 3    Day 4    Day 5
  VBlock001    vm001         29.2                     55              15        45          6           55        25
  VBlock002    vm002         15.5                     25                2        15        20           25    
  

  Plese help me to solve this issue

• Thom A

  SSC Guru

  Points: 99143

  More actions

  March 13, 2017 at 1:54 pm

  #1932953

  Personally, I would create my query, something like below, and then use my presentation layer (SSRS & a matrix, Excel & a pivot table, for example), and pivot the data.
  SELECT ST.VBLOCK, ST.VM,
   CAST(DATEDIFF(DAY, MIN(ST.CreatedDate) OVER (PARTITION BY ST.VBLOCK, ST.VM), ST.CreatedDate) + 1 AS varchar(3)) AS KPI,
   ST.CPU AS CPU
FROM Sampletable ST
UNION
SELECT ST.VBLOCK, ST.VM,
   'Avg' AS KPI,
   AVG(ST.CPU) AS CPU
FROM Sampletable ST
GROUP BY ST.VBLOCK, ST.VM
UNION
SELECT ST.VBLOCK, ST.VM,
   'Max' AS KPI,
   MAX(ST.CPU) AS CPU
FROM Sampletable ST
GROUP BY ST.VBLOCK, ST.VM;

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• DesNorton

  SSC-Insane

  Points: 24614

  More actions

  March 13, 2017 at 1:54 pm

  #1932954

  The following code will work, so long as you only have 5 days per VM.
  
WITH cteDayNum AS (
SELECT *
  , [DayNum] = ROW_NUMBER() OVER (PARTITION BY [VBLOCK], [VM] ORDER BY [CreatedDate])
FROM [dbo].[Sampletable]
)
SELECT
  [VBLOCK]
, [VM]
, [CPU Average] = CAST(AVG(CPU /1.0) AS DECIMAL(3,1))
, [CPU Max]  = MAX(CPU)
, [Day 1]   = SUM(CASE [DayNum] WHEN 1 THEN [CPU] ELSE 0 END)
, [Day 2]   = SUM(CASE [DayNum] WHEN 2 THEN [CPU] ELSE 0 END)
, [Day 3]   = SUM(CASE [DayNum] WHEN 3 THEN [CPU] ELSE 0 END)
, [Day 4]   = SUM(CASE [DayNum] WHEN 4 THEN [CPU] ELSE 0 END)
, [Day 5]   = SUM(CASE [DayNum] WHEN 5 THEN [CPU] ELSE 0 END)
FROM cteDayNum
GROUP BY [VBLOCK], [VM]


  ---

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