Monday, June 14, 2010

T-SQL: PIVOT Operator with Dynamic Columns

In My Previous post "PIVOT Operator in SQL", I explained about, How to use T-SQL Operator PIVOT.

http://blog-mstechnology.blogspot.com/2009/06/pivot-operator-in-sql.html

In Previous post, I explained using Static PIVOT Columns in the Query, which are hard coded.

This post explains with dynamic columns in PIVOT, For Example: Distinct values from columns of a table.

I used the AdventureWorks Database in my samples script

Have a look at this Query, Here I Explained in 4 steps alsong with the Query.

USE AdventureWorks
GO

#1. Declare a Variable to Store All the Column Values
DECLARE @columns VARCHAR(8000)

#2. Columns Vlaues are Storing in @columns Values In ID or QUOTED_ID Format


SELECT
@columns =
COALESCE
(
@columns + ',[' + cast(DATEPART(yyyy, OrderDate) as varchar) + ']',
'[' + cast(DATEPART(yyyy, OrderDate) as varchar)+ ']'
)
FROM
Sales.SalesOrderHeader
GROUP BY
DATEPART(yyyy, OrderDate)
ORDER BY DATEPART(yyyy, OrderDate)


@columns Contains:
#3. Building PIVOT Query with Dynamic Columns(@columns)

DECLARE @query VARCHAR(8000)
SET @query = 'SELECT *
FROM
(
SELECT
CustomerID,
DATEPART(yyyy, OrderDate) as OrderYear,
TotalDue
FROM
Sales.SalesOrderHeader
) PIV
PIVOT
(
SUM(TotalDue) FOR OrderYear in ('
+ @columns +
')
) AS chld'


#4. Executing Built Query
EXECUTE (@query)
GO
Output:
Hope it Helps you!

8 comments:

  1. What is the limit on the number of columns supported?

    ReplyDelete
  2. Wonderful, thank you so much, that was really helpful.

    ReplyDelete
  3. Wrote a really cool bit of dynamic T-SQL today.....not sure there is anything like this out there. I think its very cool! But using UNPIVOT and COALESCE, I can dynamically pull a list of columns from any table and associated column values for any record in a record listing and combine them in a list of column names with values by row. The power of this is amazing....you can use it to generate all kinds of lists, projects, applications, etc from your databases where the columns and their data types are unknown up front but need to be generated dynamically:


    Here is the code. Just drop in your database and table name. The column/value table will be generated for you in SQL Server:



    -- This gets a list of all kknown columns in your database, dynamically...
    DECLARE @COLUMNS nvarchar(max)
    SELECT @COLUMNS =

    CASE
    WHEN A.DATA_TYPE = 'nvarchar' OR A.DATA_TYPE = 'ntext' THEN
    COALESCE(@COLUMNS + ',','') + 'CAST(CONVERT(nvarchar(4000),['+A.[name]+']) AS sql_variant) AS ['+A.[name]+']'
    WHEN A.DATA_TYPE = 'datetime' OR A.DATA_TYPE = 'smalldatetime' THEN
    COALESCE(@COLUMNS + ',','') + 'CAST(CONVERT(nvarchar,['+A.[name]+'],101) AS sql_variant) AS ['+A.[name]+']'
    ELSE
    COALESCE(@COLUMNS + ',','') + 'CAST(['+A.[name]+'] AS sql_variant) AS ['+A.[name]+']'
    END

    FROM
    (
    SELECT
    A.name,
    C.DATA_TYPE
    FROM YOURDATABASENAME.dbo.syscolumns A
    INNER JOIN YOURDATABASENAME.dbo.sysobjects B ON B.id = A.id
    LEFT JOIN
    (
    SELECT
    COLUMN_NAME,
    DATA_TYPE
    FROM YOURDATABASENAME.INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'YOURTABLENAME'
    ) C ON C.COLUMN_NAME = A.name
    WHERE B.name = 'YOURTABLENAME'
    AND C.DATA_TYPE <> 'timestamp'
    ) A
    -- Test that the formatted columns list is returned...
    --SELECT @COLUMNS

    -- This gets a list of all kknown columns in your database, dynamically...
    DECLARE @COLUMNS2 nvarchar(max)
    SELECT @COLUMNS2 = COALESCE(@COLUMNS2 + ',','') + '['+A.[name]+']'
    FROM
    (
    SELECT
    A.name,
    C.DATA_TYPE
    FROM YOURDATABASENAME.dbo.syscolumns A
    INNER JOIN YOURDATABASENAME.dbo.sysobjects B ON B.id = A.id
    LEFT JOIN
    (
    SELECT
    COLUMN_NAME,
    DATA_TYPE
    FROM YOURDATABASENAME.INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'YOURTABLENAME'
    ) C ON C.COLUMN_NAME = A.name
    WHERE B.name = 'YOURTABLENAME'
    AND C.DATA_TYPE <> 'timestamp'
    ) A
    -- Test that the formatted columns list is returned...
    --SELECT @COLUMNS2


    -- Now plug in the list of the dynamic columns list into an UNPIVOT to get a Column Name / Column Value list table...
    DECLARE @sql nvarchar(max)
    SET @sql =
    '
    SELECT
    ColumnName,ColumnValue
    FROM
    (
    SELECT
    '+@COLUMNS+'
    FROM YOURDATABASENAME.dbo.YOURTABLENAME
    WHERE CHANGE_ID IN (SELECT ChangeId FROM YOURDATABASENAME.dbo.OperatorProcess WHERE OperatorProcessID = 3)
    ) AS SourceTable
    UNPIVOT
    (
    ColumnValue FOR ColumnName IN ('+@COLUMNS2+')
    ) AS PivotTable
    '

    EXEC (@sql)

    ReplyDelete
  4. Nice & simple,thnx,it works for me....

    Mehboob Ali

    ReplyDelete
  5. This comment has been removed by a blog administrator.

    ReplyDelete
  6. nice post. i also write on the same topic:
    http://rohit-developer.blogspot.in/2014/04/union-example-in-sql-server-in-this.html

    ReplyDelete
  7. The wordpress best seo plugin is the ebst option to have great site. This will provide valuable content to the reader.

    ReplyDelete