is possible to unpivot a sql server table using headers as a column and values as another column?

Question

I have a table like this:

TableName	dates	ModelName	BaseUnitPerPallet	pallet
Calendar	June	Null	4	1
Country	June	Null	2	6
Product	June	DOWNSTREAM	Null	8
ProductBOM	June	DOWNSTREAM	9	9

and I want a table like this:

Columns	values
TableName	Calendar
TableName	Country
TableName	Product
TableName	ProductBOM

where columns field is the headers of the previous table, and values are the values in an unpivot way.

I have been trying without success the unpivot logic:

SELECT Columns, Values
FROM
(
SELECT  TableName, dates, ModelName, BaseUnitPerPallet, pallet
FROM Database
as source_query
)
UNPIVOT
(
Values FOR Columns IN ( TableName, dates, ModelName, BaseUnitPerPallet, pallete)
)
as pivot_results

any advice or guidance would be great. Additionally, any resource to do this dinamic? and apply the logic without write the column names?

Thanks in advance¡

Answer 1

I'd recommend using APPLY to unpivot your table

Unpivot using APPLY

DROP TABLE IF EXISTS #YourTable
CREATE TABLE #YourTable (
     ID INT IDENTITY(1,1) PRIMARY KEY
    ,TableName VARCHAR(100)
    ,Dates Varchar(25)
    ,ModelName VARCHAR(100)
    ,BaseUnitPerPallet TINYINT
    ,Pallet TINYINT
)

INSERT INTO #YourTable
VALUES 
('Calendar','June',NULL,4,1)
,('Country','June',NULL,2,6)
,('Product','June','DOWNSTREAM',NULL,8)
,('ProductBOM','June','DOWNSTREAM',9,9)

SELECT A.ID,B.*
FROM #YourTable AS A
CROSS APPLY 
    (VALUES 
        ('TableName',A.TableName)
        ,('Dates',A.Dates)  
        ,('ModelName',A.ModelName)  
        ,('BaseUnitPerPallet',CAST(A.BaseUnitPerPallet AS Varchar(100)))
        ,('Pallet',CAST(A.Pallet AS Varchar(100)))  
    ) AS B(ColumnName,Val)
--WHERE B.Val IS NOT NULL /*Optional in case you want to ignore NULLs*/
ORDER BY A.ID,B.ColumnName