How to pivot in SQL without setting values | TSQL
In this video I show you how to pivot without the need to set values in the pivot. I know I use the word dynamic a lot, however, please think of this as a semi-dynamic pivot because I did not use a pre-built variable to hold column names. This pivot is useful when you do not know what values you will be working with.
Here is the Final code:
==============================================================
SELECT [ProductID], [Description], [ListPrice1], [ListPrice2],
[ListPrice3], [ListPrice4], [ListPrice5], [ListPrice6]
FROM
(
SELECT PH.[ProductID], P.[Name] AS [Description], PH.[ListPrice], RowOrder = CAST('ListPrice' AS VARCHAR) +
CAST(ROW_NUMBER() OVER(PARTITION BY PH.[ProductID], P.[Name] ORDER BY PH.[ProductID], P.[Name] ASC) AS VARCHAR)
FROM [AdventureWorks2022].[Production].[ProductListPriceHistory] AS PH
INNER JOIN [AdventureWorks2022].[Production].[Product] AS P ON PH.ProductID = P.ProductID
--WHERE ([ProductID] = '885'
) AS [SubSelect]
PIVOT
(
MIN([ListPrice])
FOR RowOrder IN ([ListPrice1],[ListPrice2],[ListPrice3],[ListPrice4],[ListPrice5],[ListPrice6])
) AS [PIVOT]
==============================================================
My next video will be about Meshtastic and will be coming soon. I did not realize the scope of variables necessary when collecting ranges at first.
As always thank you so much for watching my videos!! I hope you had a great Christmas and will have a very happy new year. Don't perish and I will see you in the next video!