Pivot 2 columns

  • 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


  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply