Converting rows into columns

  • I have a table that contains three columns with ID, dates and value. I want to convert the contents into rows so that the date column will become a row and the FldVal will become the contents. Date and FldVal could contain different values. I think I can use Pivot, but not sure what to give in the in statement. Any help is greatly appreciated. If there is a better way to do this without Pivot, that will be great too.

     

    CREATE TABLE #t1([VID] [varchar](100) NULL,[OrdDate] [datetime] NOT NULL, [FldVal] int NULL ) ON [PRIMARY]

    insert into #t1 values('111','2022-01-10',10)
    insert into #t1 values('111','2022-01-11',1)
    insert into #t1 values('111','2022-01-12',2)

    Select VID from #t1 PIVOT(MAX([FldVal]) FOR [OrdDate] in ([What should I give here])) as P

    drop table #t1

    • This topic was modified 1 year, 5 months ago by  don075.
  • CREATE TABLE #t1([VID] [varchar](100) NULL,[OrdDate] [datetime] NOT NULL, [FldVal] int NULL ) ON [PRIMARY]

    insert into #t1 values('111','2022-01-10',10)
    insert into #t1 values('111','2022-01-11',1)
    insert into #t1 values('111','2022-01-12',2)

    -- Generate dynamic column names with actual dates
    DECLARE @Columns NVARCHAR(MAX)
    SELECT @Columns = STUFF((
    SELECT DISTINCT ', [' + CONVERT(NVARCHAR(10), OrdDate, 120) + ']'
    FROM #t1
    FOR XML PATH('')
    ), 1, 2, '')

    -- Generate dynamic SQL query
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = '
    SELECT VID, ' + @Columns + '
    FROM (SELECT VID, OrdDate, FldVal
    FROM #t1
    ) AS SourceTable
    PIVOT (MAX(FldVal) FOR OrdDate IN (' + @Columns + ')
    ) AS PivotData'

    -- Execute dynamic SQL query
    EXEC sp_executesql @SQL

    DROP TABLE #t1
  • Thanks a lot. Much appreciated.

  • don075 wrote:

    Thanks a lot. Much appreciated.

    Do you understand how it works?

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I am trying commands separately to understand. Will update if I have any questions.

  • don075 wrote:

    I am trying commands separately to understand. Will update if I have any questions.

    This is the dynamic SQL that is created:

    SELECT VID, [2022-01-10], [2022-01-11], [2022-01-12]
    FROM (SELECT VID, OrdDate, FldVal
    FROM #t1
    ) AS SourceTable
    PIVOT (MAX(FldVal) FOR OrdDate IN ([2022-01-10], [2022-01-11], [2022-01-12])
    ) AS PivotData

Viewing 6 posts - 1 through 5 (of 5 total)

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