Click here to monitor SSC

Software Architect - Red Gate Software

Pivots with Dynamic Columns in SQL Server 2005

Published 14 September 2007 3:28 am

Pivots in SQL Server 2005 can rotate a table, i.e. they can turn rows into columns. PIVOTs are frequently used in reports, and they are reasonably simple to work with. However, I’ve seen quite a few questions about this operator. Most questions were about the column list in the PIVOT statement. This list is fixed, but many times the new columns are determined by the report at a later stage. This problem is easily solved when we mix pivots with dynamic SQL, so here is a very simple example about how to dynamically generate the pivot statement:

PIVOT allows you to turn data rows into columns. For example, if you have a table like this (I use only three months here for simplicity):

CREATE TABLE Sales ([Month] VARCHAR(20) ,SaleAmount INT)

INSERT INTO Sales VALUES (‘January’, 100)
INSERT INTO Sales VALUES (‘February’, 200)
INSERT INTO Sales VALUES (‘March’, 300)

SELECT FROM SALES
 

Month             SaleAmount
—————-  ———–
January           100
February          200
March             300 

Suppose we wanted to convert the above into this:

 
January     February    March
———– ———-  ———-
100         200         300

We can do this using the PIVOT operator, as follows:

SELECT  [January]
      [February]
      [March]
FROM    SELECT    [Month]
                  SaleAmount
          FROM      Sales
        p PIVOT SUM(SaleAmount)
                    FOR [Month] 
IN ([January],[February],[March])                   ) AS pvt

However, in the above example, I have the column names fixed as the first three months. If I want to create a result in which the columns are dynamic (for example, they are read from a table), then I need to construct the above query dynamically. To demonstrate this let’s look at the following example:

In the first table I have the column names I want to use:

CREATE TABLE Table1 (ColId INT,ColName VARCHAR(10))
INSERT INTO Table1 VALUES(1, ‘Country’)
INSERT INTO Table1 VALUES(2, ‘Month’)
INSERT INTO Table1 VALUES(3, ‘Day’)

In the second table I have the data. This consists of a row identifier (tID), a column ID (ColID) that refers to the column type in Table1, and a value:

CREATE TABLE Table2 (tID INT,ColID INT,Txt VARCHAR(10))

INSERT INTO Table2 VALUES (1,1, ‘US’)
INSERT INTO Table2 VALUES (1,2, ‘July’)
INSERT INTO Table2 VALUES (1,3, ’4′)
INSERT INTO Table2 VALUES (2,1, ‘US’)
INSERT INTO Table2 VALUES (2,2, ‘Sep’)
INSERT INTO Table2 VALUES (2,3, ’11′)
INSERT INTO Table2 VALUES (3,1, ‘US’)
INSERT INTO Table2 VALUES (3,2, ‘Dec’)
INSERT INTO Table2 VALUES (3,3, ’25′)

Now I would like to retrieve data from these two tables, in the following format:


tID         Country    Day        Month
———– ———- ———- ———-
1           US         4          July
2           US         11         Sep
3           US         25         Dec 

In other words I want to turn the data rows in Table2 into columns. If I had a fixed set of columns for the result, i.e. the columns Country, Day, and Month were fixed, I could use SQL Server 2005’s PIVOT operator in a query like:

SELECT  tID
      [Country]
      [Day]
      [Month]
FROM    SELECT    t2.tID
                  t1.ColName
                  t2.Txt
          FROM      Table1 AS t1
                    JOIN Table2 
AS t2 ON t1.ColId t2.ColID         p PIVOT MAX([Txt])                     FOR ColName IN [Country][Day],                                      [Month] ) ) AS pvt ORDER BY tID ;

However I need to construct this query dynamically, because the column names Country, Day, and Month are specified in a table, and can be changed independently from my query. In our case these columns are given in Table1.

In the first step to generate the final pivot query I need to create the list of columns, in this case [Country], [Day], [Month].

Since there is no string concatenation aggregator in SQL (a concatenation aggregator would not be deterministic without some order restriction), and since the column names are stored in rows of a table, I need to flatten these columns into a single row or variable. There are various solutions to achieve this. One solution would be to use a query like:

DECLARE @cols NVARCHAR(2000)
SELECT  @cols COALESCE(@cols ‘,[' colName ']‘,
                         ‘[' colName ']‘)
FROM    Table1
ORDER BY colName

This query works both on SQL Server 2000 and 2005. It is efficient, but some may not like it because it uses the same variable (@cols) on both sides of an assignment. Another solution that works on SQL Server 2005 only is to use XML PATH.

DECLARE @cols NVARCHAR(2000)
SELECT  @cols STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                ‘],[' t2.ColName
                        FROM    Table1 AS t2
                        ORDER BY '],[' t2.ColName
                        FOR XML PATH('')
                      ), 12'') + ']‘

This second query (I’ve seen this posted by Peter Larsson) has the advantage that it does not use the @cols variable on the right hand side. I like this solution more, since this can be extended as a general string concatenation aggregate in more complex queries.

Both of the above queries generate, from Table1, the string: ‘[Country],[Day], [Month]’. This column list is used twice in the pivot query that we aim to construct. Once it is use in the list of columns that we want to retrieve, and once it is used as the list of values that should become columns. Having constructed this list of columns above, we can just concatenate it with the missing parts of the pivot query like:

DECLARE @query NVARCHAR(4000)
SET @query N’SELECT tID, ’+
@cols +‘
FROM
(SELECT  t2.tID
      , t1.ColName
      , t2.Txt
FROM    Table1 AS t1
        JOIN Table2 AS t2 ON t1.ColId = t2.ColID) p
PIVOT
(
MAX([Txt])
FOR ColName IN
( ’+
@cols +‘ )
) AS pvt
ORDER BY tID;’

Executing this with

EXECUTE(@query)

will give us the expected result: a table that is pivoted and shows columns that were specified in a table:

tID         Country    Day        Month
———– ———- ———- ———-
1           US         4          July
2           US         11         Sep
3           US         25         Dec

34 Responses to “Pivots with Dynamic Columns in SQL Server 2005”

  1. Adam Machanic says:

    I know this is just a touch on the anal-retentive side, but as currently written your queries are technically a vector for an injection attack (think of a malnamed column). To thwart this issue, please use the QUOTENAME function instead of concatenating square brackets on either side of the column name.

  2. Andras says:

    There is no such thing like paranoia :) . You are right Adam. While in my example the columns are not meant to come form a user directly, and I assumed it is “clean”, there is no reason not to fortify code by default. Thanks for your comment.
    Andras

  3. Cheverton says:

    Your approach is clever and instructive but isn’t the following simpler?

    DECLARE @query NVARCHAR(4000)

    Select @query = N’SELECT tID
    , [1] as ['+ a.ColName + ']
    , [2] as ['+ b.ColName + ']
    , [3] as ['+ c.ColName + ']
    FROM ( SELECT t2.tID
    , t2.ColId
    , t2.Txt
    FROM Table2 t2
    ) p PIVOT ( MAX([Txt])
    FOR ColId IN ( [1], [2], [3] ) ) AS pvt
    ORDER BY tID ;’
    From
    Table1 a, Table1 b, Table1 c
    where
    a.ColId=1 and b.ColId=2 and c.ColId=3;

    exec( @query)

  4. Cheverton says:

    I needed to think 2 seconds more. Of course, your approach extends dynamically for any number of columns, not just any names of columns.

  5. Praddumna says:

    Hi
    Can we insert a new column in our query result and have a default value to it.

  6. Ferenc says:

    Dear Andras!

    For me isn’t working.
    It is said The name ‘Select …’ is not a valid identifier.

    When I copy the @query content SQL syntax, and run in a new query it’s completted succesfully.

    Can you help me?

  7. suruchijain says:

    Hi András!

    Well I tried using ur tick, I guess the “pivot” keyword in the @query is not accepted by SQL Server 2000. the error I get on executing :

    this is what I do :

    DECLARE @cols NVARCHAR(2000)
    SELECT @cols = COALESCE(@cols + ‘,[' + colName + ']‘,
    ‘[' + colName + ']‘)
    FROM Table1
    ORDER BY colName
    –print @cols

    DECLARE @query NVARCHAR(4000)
    SET @query = N’SELECT tID, ‘+
    @cols +’
    FROM
    (SELECT t2.tID
    , t1.ColName
    , t2.Txt
    FROM Table1 AS t1
    JOIN Table2 AS t2 ON t1.ColId = t2.ColID) p
    PIVOT
    (
    MAX([Txt])
    FOR ColName IN
    ( ‘+
    @cols +’ )
    ) AS pvt
    ORDER BY tID;’

    exec @query

    and the error I get is:

    Server: Msg 203, Level 16, State 2, Line 26
    The name ‘SELECT tID, [Country],[Day],[Month]
    FROM
    (SELECT t2.tID
    , t1.ColName
    , t2.Txt
    FROM Table1 AS t1
    JOIN Table2 AS t2 ON t1.ColId = t2.ColID) p
    PIVOT
    (
    MAX([Txt])
    FOR ColName IN
    ( [Country],[Day],[Month] )
    ) AS pvt
    ORDER BY tID;’ is not a valid identifier.

    Could u plz tell me where I m going wrong ?

  8. Ferenc says:

    Hi Suruchijain!

    The solution is: Execute(@query) instead of exec @query!

  9. paulie says:

    This is great, kudos to you sir! I’ve been searching for a solution like this for ages. This is a real enabler, thanks so much for sharing your knowledge.

  10. thot says:

    Hi!

    This is a great article.

    But i’m having problems to do this in my query.

    I doing and quiz analysis and i want to do this dinamically.

    i have 2 tables in the DB like this:
    — Aswear (IDAsw, IDQuiz, IDOption, IDQuestion)
    — Option (IDOption, IDQuestion, Item, Order)

    And i want something like this:

    Sex | Yes | No
    M | 2 | 2
    F | 3 | 6

    The column “Sex” have IDQuestion = 1 and the other question have only 2 option (Yes or No) and have the IDQuestion = 11

    This is possible by using pivot??
    Anyone can showme an example to get this??

    Regards

  11. alangrn says:

    Thanks. This is a great article. How do I wriet these results to a table? I am trying to get the results from the cross tab to be linked to another query.

  12. BRebey says:

    Excellent article! Thanks a million for all the work!

    If you ever discover a way to use dynamic columns without the size limitation of a local variable, please let me know! This solution won’t work for me, as my requirements are to support 2880 columns – i.e., two days of data samples taken at 1-minute intervals, with each time interval in a column.

    Thanks again!

  13. gopikrishna says:

    Hi,Thanqs for the above..query..lot of useful but can we implement the same
    withot putting the query in single quotes.

  14. gopikrishna says:

    Thanqs andras.
    For
    Pivots with Dynamic Columns in SQL Server 2005
    But can u give a solution for
    PIVOT with dynamic columns but Without dynamic sql statements

    bye.
    reply me at:
    krishnak70@yahoo.co.in

  15. JoeManJoe says:

    I have a SQL SERVER 2005 table with 3 columns: Operator, Shift, and Date. Rows are like:
    Operator Shift Date
    John Early

    How can i use PIVOT to create a SQL SERVER 2005 report that lists the days of a month as columns and the o

  16. JoeManJoe says:

    I have a SQL SERVER 2005 table with 3 columns: Operator, Shift, and Date. Rows are like:
    Operator Shift Date
    John Early

    How can i use PIVOT to create a SQL SERVER 2005 report that lists the days of a month as columns and the o

  17. JoeManJoe says:

    I have a SQL SERVER 2005 table with 3 columns: Operator, Shift, and Date.
    Sample rows are :
    Operator Shift Date
    John Early Oct. 1, 2008
    Mary Late Oct. 1, 2008
    John Late Oct. 2, 2008
    Mary Early Oct. 2, 2008

    How can i use PIVOT or Crosstab to create a SQL SERVER 2005 report that lists ALL the days of a month as columns and the operators as rows – one row for each operator. More importantly in the row-column intersection, I will like to see Shifts instead of aggregate data (as the shift field is non numeric). I want something like this:

    Oct 1 Oct 2 Oct 3 …………………………
    John Early Late
    Mary Late Early

  18. orbitus says:

    Using this fantastic article I was able to make this procedure for making dynamic crosstabs. Its not protected against SQL injection or anything. Its just a simple procedure to get data like you would be able to in MS Access. I hope this can be of help to some, others may find ways to improve it.

    Chris

    create procedure UberCrosstab
    @pivotRowFields as nvarchar(1000),
    @pivotField as nvarchar(100),
    @pivotTable as nvarchar(1000),
    @aggField as nvarchar(100),
    @aggFunc as nvarchar(10),
    @pivotWhere as nvarchar(1000) = Null,
    @pivotOrder as nvarchar(100) = Null,
    @colField as nvarchar(100) = Null,
    @colTable as nvarchar(100) = Null,
    @colWhere as nvarchar(300) = Null,
    @colTop as nvarchar(10) = Null
    AS

    /*
    ———————————————————————-
    UberCrossTab for SQL Server 2005
    ———————————————————————-
    Created by Chris Gruel (cgruel at g mail dot com)
    3/23/2009
    ———————————————————————-
    The declarations below have been retained for explanation of
    UberCrossTab’s variables and for easy debugging if needed.
    ———————————————————————-
    –*/

    /*
    –This is the field name of where your dynamic columns will come from
    –If left null then the pivot table data will be used
    –Use this only if you want to use a table that is different than the pivot table/view you are getting your data from for column names
    –(In Access this is called the Column Heading on a Crosstab Query)
    declare @colField as nvarchar(100)

    –This is the table name of the table you want to pull your column names from
    –Use this only if you want to use a table that is different than the pivot table/view you are getting your data from for column names
    declare @colTable as nvarchar(100)

    –Use this only if you want to use filter from the optional table that is different than the pivot table/view you are getting your data from
    declare @colWhere as nvarchar(300)

    –Use this to limit the number of columns you are pulling your column names from
    –Use this only if you want to use a table that is different than the pivot table/view you are getting your data from for column names
    declare @colTop as nvarchar(10)

    /* This is the section of the pivot data */
    –This is the comma seperated list of field(s) from the pivot table/view to include with the data
    –(In Access these are called the Row Heading on a Crosstab Query)
    – eg. ‘column1,column2,column3′ or ‘column1′
    declare @pivotRowFields as nvarchar(1000)

    –This is the field in the pivot table/view that matches the
    declare @pivotField as nvarchar(100)

    –This is the table/view for the pivot
    –This can also be an entire query wrapped in parentheses
    declare @pivotTable as nvarchar(1000)

    –This is the optional WHERE clause for the pivot table/view
    declare @pivotWhere as nvarchar(300)

    –This is the optional ORDER BY statement for the pivot table/view
    declare @pivotOrder as nvarchar(100)

    –This is the field in the pivot table/view that will be aggregated
    –(In Access this is called the Value Field on a Crosstab Query)
    declare @aggField as nvarchar(100)

    –This is the function to perform on the aggregate field
    – eg. SUM or MAX or MIN or AVG or any valid aggregate function
    declare @aggFunc as nvarchar(10)
    –*/

    –This is the variable that stores all the column names
    declare @cols as nvarchar(max)

    If object_id(‘tempdb..#cols’) is not null
    drop table #cols

    create table #cols (Col nvarchar(max))

    declare @query as nvarchar(max)

    /* This is example data
    –set @colField = ‘fw’
    –set @colTable = ‘fscl_yr_wk’
    –set @colWhere = ‘fw between (select min(fw) from CC_MKDN_DLRS) AND (SELECT max(fw) from CC_MKDN_DLRS)’
    set @colTop = ’17′

    set @pivotRowFields = ‘SubDpt’
    set @pivotField = ‘fw’
    set @pivotTable = ‘CC_MKDN_DLRS’
    set @aggField = ‘[$MKDN]‘
    set @aggFunc = ‘SUM’
    –set @pivotWhere = ‘subdpt = ”24”’
    set @pivotOrder = ‘SubDpt’
    –*/

    set @colField = case when @colField is null then @pivotField else @colField end
    set @colTable = case when @colTable is null then @pivotTable else @colTable end
    set @colWhere = case when @colField is null and @pivotWhere is not null then @pivotWhere else @colWhere end

    set @query = ‘
    DECLARE @cols NVARCHAR(max)
    SELECT @cols = STUFF(
    ( SELECT DISTINCT ‘ + case when @colTop is not null then ‘Top ‘ + @colTop else ” end + ”’],['' + t2.' + @pivotField + '
    FROM
    (SELECT CONVERT(nvarchar(max),a.' + @colField + ') AS ' + @pivotField + '
    FROM
    (SELECT DISTINCT ' + @colField + '
    FROM ' + @colTable + '
    ' + case when @colWhere is not null then 'WHERE ' + @colWhere else '' end + '
    ) AS a
    ) AS t2
    ORDER BY ''],['' + t2.' + @pivotField + ' FOR XML PATH('''')
    ), 1, 2, '''') + '']”
    select @cols

    insert into #cols
    exec (@query)

    set @cols = (select * from #cols)

    drop table #cols

    DECLARE @query2 NVARCHAR(max)
    SET @query2 = N’SELECT ‘ + @pivotRowFields + ‘, ‘+
    @cols + ‘
    FROM
    (SELECT ‘ + @pivotRowFields + ‘
    ,’ + @pivotField + ‘
    ,’ + @aggField + ‘
    FROM ‘ + @pivotTable + ‘ as a
    ‘ + case when @pivotWhere is not null then ‘WHERE ‘ + @pivotWhere else ” end + ‘) p
    PIVOT
    (
    ‘ + @aggFunc + ‘(‘ + @aggField + ‘)
    FOR ‘ + @pivotField + ‘ IN
    ( ‘+
    @cols +’ )
    ) AS pvt
    ‘ + case when @pivotOrder is not null then ‘Order by ‘ + @pivotOrder else ” end + ‘;’

    exec (@query2)

  19. reach2shaik says:

    Hi Andras

    I am using the below query in MS SQL SERVER 2005

    SELECT ExamName,StudentName,StudentID FROM
    (SELECT ExamName,StudentName,StudentID,Marks,SubjectName FROM #MyTable2)p
    PIVOT
    (SUM([Marks]) for SubjectName in ([SCIENCE],[SOCIAL],[TELUGU]))
    AS pvt ORDER BY StudentID

    #MyTable2 contains ExamName,StudentName,StudentID,Marks,SubjectName columns. I need to display the result as follows

    ExamName StudentName StudentID Subject1Marks Subject2Marks
    —————————————————————————–
    EXAM1 Student1 1 50 75
    EXAM1 Student2 2 35 45

    At the place of [SCIENCE],[SOCIAL],[TELUGU] these are the subject names

    But i am getting the following result. Can you please tell me where i am missing?

    ExamName StudentName StudentID
    ——————————————
    EXAM1 Student1 1
    EXAM1 Student2 2

  20. wim3025 says:

    I would like to thank Andras very much. This posting did help me a lot on a problem i couldn’t get solved on my own!!!! It worked perfect for me.

  21. Chris M says:

    Great article. I need to extend this functionality to put the data into a hash table, however I keep getting #temp does not exist. Please bear in mind that I cannot declare the table beforehand as I don’t know how many there will be as this is dynamic.

  22. larryv says:

    This post was very helpful and I created a powerpoint citing it as an example to share with my dev team. I do have a few questions.

    1) How can you modify it to use the QUOTENAME function to make it more secure (as Adam stated in the first comment).

    2) I was asked to make some modifications in my code (that was based on your example) that I am having a hard time wrapping my brain around. I was wondering if you can help.

    In your code, suppose your second table was actually structured like this:

    CREATE TABLE Table2 (tID INT,ColID INT,Txt VARCHAR(10), Code VARCHAR(10))

    And populated thusly:
    INSERT INTO Table2 VALUES (1,1, ‘US’, ‘USA’)
    INSERT INTO Table2 VALUES (1,2, ‘July’, ‘JUL’)
    INSERT INTO Table2 VALUES (1,3, ’4′, ‘NUM’)
    INSERT INTO Table2 VALUES (2,1, ‘US’ ‘USA’)
    INSERT INTO Table2 VALUES (2,2, ‘Sep’, ‘SEP’)
    INSERT INTO Table2 VALUES (2,3, ’11′, ‘NUM’)
    INSERT INTO Table2 VALUES (3,1, ‘US’ ‘USA’)
    INSERT INTO Table2 VALUES (3,2, ‘Dec’, ‘DEC’)
    INSERT INTO Table2 VALUES (3,3, ’25′, ‘NUM’)

    The results the I am trying to get are this:

    [code]
    tID Country CODE Month CODE Day CODE
    ---- ------- ----- ------ ----- ---- -----
    1 US USA July JUL 4 NUM
    2 US USA Sep SEP 11 NUM
    3 US USA Dec DEC 25 NUM
    [/code]

    Which means 1) That the columns are no longer ordered alphabetically (although I suppose they are ordered by t2.CodID) and 2) The CODE column appears along side every “named” ColName column.

    My actual tables are based on question / answer and the “code” column is the point value of each question. It makes a little more sense given that than shoehorning it into your example but I wanted to explain it clearly.

    I can probably back out of all of this and build a solution in the middle tier but I came sooooo close with your example.

    (The cynic in me feels that given the post is from 2007 and its now 2010 I likely won’t get a response, but I am hoping)

  23. GBO says:

    This was a great posting thanks – after a lot of searching for an appropriate answer to this question this morning – this was the easist and clearest example that got me the answer i needed. Cheers Andras

  24. [...] Originally Posted by nzsquall Hi corncrowe, Thanks for your reply. I think a stored procedure will be more efficient here. Can you share me some of your ideas? Many thanks. Look up pivot or crosstab. Should give you an idea how to approach this problem. Pivots with Dynamic Columns in SQL Server 2005 [...]

  25. fabiolemoslessa says:

    Good day.

    I have a problem. I need to make a query with seguntes data:

    Result of query
    parents students
    Father1 student1.1
    Father1 student1.2
    Father1 student1.3
    Father2 student2.1
    Father2 student2.2
    Father3 student3.1
    Father3 student3.2
    Father3 student3.3
    Father3 student3.4

    for

    Parents student1 student2 student3 student4
    Father1 student1.1 student1.2 student1.3 null
    Father2 student2.1 student2.2 null null
    father3 student3.1 student3.2 student3.3 student3.4

    Could you help me. Please.
    Thanks for listening!

  26. hicham4 says:

    Hi Andras,

    I have a query which create a temporary table #object with 1 column (id int), I want to extend #object with columns from a another query results, I’ve tried cursor:
    CREATE TABLE #object(ID int)

    DECLARE @q int
    DECLARE @getid CURSOR

    SET @getid= CURSOR FOR
    select Q.Question /*, QR.ResponseText, QR.DynamicQuestionResponseID*/ from DynamicForms_Question as Q
    inner join DynamicForms_QuestionResponse as QR on Q.DynamicQuestionID = QR.DynamicQuestionID
    where Q.ModuleID = 729
    and QR.ResponseDateTime = ’2012-12-27 11:31:00′
    and QuestionType ‘Label’
    and QuestionType ‘HR’
    order by Q.SortOrder asc

    OPEN @getid
    FETCH NEXT
    FROM @getid INTO @q
    WHILE @@FETCH_STATUS = 0
    BEGIN
    Alter Table #object add — what can i use here????
    FETCH NEXT
    FROM @getid INTO @q
    END

    CLOSE @getid
    DEALLOCATE @getid

    drop table #object

    Thanks for help.
    Hicham.

Leave a Reply