Converting rows into columns

Question

Post reply

Converting rows into columns

don075

SSC-Addicted

Points: 425
More actions
June 15, 2023 at 12:38 am
Go to Answer
#4211512
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, 6 months ago by don075.

Jonathan AC Roberts

SSCoach

Points: 18363

June 15, 2023 at 12:50 am

Answer

#4211514

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

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

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

don075 SSC-Addicted Points: 425 More actions · Answer 1

don075

SSC-Addicted

Points: 425

June 15, 2023 at 1:01 am

#4211515

Thanks a lot. Much appreciated.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 2

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)

don075 SSC-Addicted Points: 425 More actions · Answer 3

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

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 4

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