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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy