Pivot 2 columns

• rash3554

  SSCommitted

  Points: 1984

  More actions

  March 21, 2018 at 8:04 am

  #336740

  Hi All,

    I need some help with pivoting 2 columns. Below is my input table
  Create table dbo.Vehicles(FieldName VARCHAR(100), RecordCount Int, Result varchar(200),DateAdded datetime)
INSERT Into dbo.Vehicles Values('Top Cars',100,'Coupe','2018-01-02')
INSERT Into dbo.Vehicles Values('Top Cars',200,'Sedan','2019-01-02')
INSERT Into dbo.Vehicles Values('Top Bikes',5500,'Hybrid','2018-02-02')
INSERT Into dbo.Vehicles Values('Top Bikes',6600,'Touring','2019-03-02')
INSERT Into dbo.Vehicles Values('Top Trains',60,'Electric','2018-03-02')
INSERT Into dbo.Vehicles Values('Top Trains',50,'Electric','2019-03-02')

  This is the expected output.
  Create table dbo.TopVehicles(FieldName VARCHAR(100),OldCount int,newCount int,OldResult varchar(200), NewResult Varchar(200))
INSERT INTO dbo.TopVehicles Values('Top cars',100,200,'Coupe','Sedan')
INSERT INTO dbo.TopVehicles Values('Top Bikes',5500,6600,'Hybrid','Touring')

  This is the code that I currently have
  SELECT FieldName,ISNULL([1],0) as [OldCount], ISNULL([2],0) as [NewCount]
FROM
(
  SELECT [FieldName]
   , CONVERT(BIGINT, RecordCount) AS Ct
   , Result
   ,DateAdded
   ,ROW_NUMBER() OVER(PARTITION BY Fieldname, Result ORDER BY DateAdded DESC) AS [ID]
  FROM dbo.Vehicles
  ) Rownum
  PIVOT(MAX(Ct) FOR [ID] IN([1] , [2])) AS PIV1
      
      How can I add OldResult and NewResult?

  Thank you
  MR

• ScottPletcher

  SSC Guru

  Points: 101121

  More actions

  March 21, 2018 at 2:30 pm

  #1984196

  
SELECT FieldName,
  MAX(CASE WHEN ID = 2 THEN RecordCount END) AS OldCount,
  MAX(CASE WHEN ID = 1 THEN RecordCount END) AS NewCount,
  MAX(CASE WHEN ID = 2 THEN Result END) AS OldResult,
  MAX(CASE WHEN ID = 1 THEN Result END) AS NewResult
FROM
(
SELECT *
  ,ROW_NUMBER() OVER(PARTITION BY Fieldname ORDER BY DateAdded DESC) AS [ID]
FROM dbo.Vehicles
) Rownum
WHERE ID IN (1, 2)
GROUP BY FieldName
ORDER BY FieldName

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".