Gday All,
I've written some code to dynamically Pivot a table like SQL Server : Transpose rows to columns
The code looks like this
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX)
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(FieldName)
FROM CORE_Items_Extra
WHERE Not(FieldName = '')
ORDER BY 1
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'')
SET @sql = 'SELECT ItemID, ' + @cols + '
FROM
(
SELECT ItemID, FieldValue, FieldName
FROM CORE_Items_Extra
) AS SourceTable
PIVOT
(
MAX(FieldValue) FOR FieldName IN (' + @cols + ')
) AS PivotTable;'
EXECUTE(@sql)
Which works perfectly BUT I want to use it within a View, I've tried copying the code to a view and it works but wont save as it doesn't like the Declare statements in the view, I've got it working in a Stored Procedure but cant use Stored Procedures in a View, I think I need to have it as a Table-Valued Function but cant use the Execute statement within a TBF. I need to combine this data with another table in a view, and would like to keep it dynamic, so any ideas would be greatly appreciated :) We are using SQL 2008 R2
You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output.
You can also create a dynamic pivot query, which uses a dynamic columns for pivot table, means you do not need to pass hard coded column names that you want to display in your pivot table. Dynamic pivot query will fetch a value for column names from table and creates a dynamic columns name list for pivot table.
Introduction to SQL Server PIVOT operator First, select a base dataset for pivoting. Second, create a temporary result by using a derived table or common table expression (CTE) Third, apply the PIVOT operator.
I suggest you to create some view (myView
in sample below) and then write stored procedure that will alter your view like:
CREATE VIEW myView
AS
SELECT 1
GO
CREATE PROCEDURE myStoredProc
AS
BEGIN
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX)
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(FieldName)
FROM CORE_Items_Extra
WHERE Not(FieldName = '')
ORDER BY 1
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'')
SET @sql = 'ALTER VIEW myView
AS
SELECT ItemID, ' + @cols + '
FROM
(
SELECT ItemID, FieldValue, FieldName
FROM CORE_Items_Extra
) AS SourceTable
PIVOT
(
MAX(FieldValue) FOR FieldName IN (' + @cols + ')
) AS PivotTable;'
EXECUTE(@sql)
END
GO
Run this SP within job once or more in a day.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With