rows to columns

• Y.B.

  SSChampion

  Points: 11649

  More actions

  June 30, 2016 at 7:57 am

  #1887879

  Charmer (6/29/2016)

  ---

  I am getting different values for DatabaseName, ReportID on same date.

  I want to show both values. But pivot choose max value.

  Possible to handle this?

  Do you mean to say that your data is more like this?

  DECLARE @Actual TABLE (ReportDate date , DatabaseName varchar(10), ReportID int , TotalSum bigint )

  INSERT INTO @Actual

  SELECT '2016-06-26', 'Test1' , 1, 150

  UNION ALL

  SELECT '2016-06-26', 'Test2' , 2, 200

  UNION ALL

  SELECT '2016-06-27', 'Test1' , 1, 150

  UNION ALL

  SELECT '2016-06-27', 'Test2' , 2, 230

  UNION ALL

  SELECT '2016-06-28', 'Test1' , 1, 100

  UNION ALL

  SELECT '2016-06-28', 'Test2' , 2, 180

  SELECT * FROM @Actual

  SELECT

  DatabaseName,

  ReportID,

  MAX(CASE WHEN ReportDate = '2016-06-26' THEN TotalSum END) AS '2016-06-26',

  MAX(CASE WHEN ReportDate = '2016-06-27' THEN TotalSum END) AS '2016-06-27',

  MAX(CASE WHEN ReportDate = '2016-06-28' THEN TotalSum END) AS '2016-06-28'

  FROM

  @Actual

  GROUP BY

  DatabaseName,

  ReportID

  I'm not suggesting the above code is a solution to your problem but rather an example.

  You need to make sure you provide realistic sample data even if it's fake. If this is not right please post proper DDL and sample and we can help.

  Now keep in mind when you 'pivot' data you are normally aggregating some kind of value. Using MAX() is kind of a way to cheat if you only have 1 value but as you realized you can't use that when you had more that one TotalSum per day.

  EDIT: I didn't notice the second page with the follow up responses. That being said I'll still leave this up in case.

  ---

  SELECT quote FROM brain WHERE original = 1
  0 rows returned