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
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
June 15, 2023 at 1:01 am
Thanks a lot. Much appreciated.
June 15, 2023 at 2:46 am
Thanks a lot. Much appreciated.
Do you understand how it works?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 15, 2023 at 6:48 pm
I am trying commands separately to understand. Will update if I have any questions.
June 15, 2023 at 7:18 pm
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