By Sean Cunningham


2012-05-01 21:05:07 8 Comments

I've been tasked with coming up with a means of translating the following data:

date        category        amount
1/1/2012    ABC             1000.00
2/1/2012    DEF             500.00
2/1/2012    GHI             800.00
2/10/2012   DEF             700.00
3/1/2012    ABC             1100.00

into the following:

date        ABC             DEF             GHI
1/1/2012    1000.00
2/1/2012                    500.00
2/1/2012                                    800.00
2/10/2012                   700.00
3/1/2012    1100.00

The blank spots can be NULLs or blanks, either is fine, and the categories would need to be dynamic. Another possible caveat to this is that we'll be running the query in a limited capacity, which means temp tables are out. I've tried to research and have landed on PIVOT but as I've never used that before I really don't understand it, despite my best efforts to figure it out. Can anyone point me in the right direction?

7 comments

@nvogel 2019-01-24 16:22:29

Updated version for SQL Server 2017 using STRING_AGG function to construct the pivot column list:

create table temp
(
    date datetime,
    category varchar(3),
    amount money
);

insert into temp values ('20120101', 'ABC', 1000.00);
insert into temp values ('20120201', 'DEF', 500.00);
insert into temp values ('20120201', 'GHI', 800.00);
insert into temp values ('20120210', 'DEF', 700.00);
insert into temp values ('20120301', 'ABC', 1100.00);


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = (SELECT STRING_AGG(category,',') FROM (SELECT DISTINCT category FROM temp WHERE category IS NOT NULL)t);

set @query = 'SELECT date, ' + @cols + ' from 
            (
                select date
                    , amount
                    , category
                from temp
           ) x
            pivot 
            (
                 max(amount)
                for category in (' + @cols + ')
            ) p ';

execute(@query);

drop table temp;

@SFrejofsky 2017-07-12 18:58:22

I know this question is older but I was looking thru the answers and thought that I might be able to expand on the "dynamic" portion of the problem and possibly help someone out.

First and foremost I built this solution to solve a problem a couple of coworkers were having with inconstant and large data sets needing to be pivoted quickly.

This solution requires the creation of a stored procedure so if that is out of the question for your needs please stop reading now.

This procedure is going to take in the key variables of a pivot statement to dynamically create pivot statements for varying tables, column names and aggregates. The Static column is used as the group by / identity column for the pivot(this can be stripped out of the code if not necessary but is pretty common in pivot statements and was necessary to solve the original issue), the pivot column is where the end resultant column names will be generated from, and the value column is what the aggregate will be applied to. The Table parameter is the name of the table including the schema (schema.tablename) this portion of the code could use some love because it is not as clean as I would like it to be. It worked for me because my usage was not publicly facing and sql injection was not a concern. The Aggregate parameter will accept any standard sql aggregate 'AVG', 'SUM', 'MAX' etc. The code also defaults to MAX as an aggregate this is not necessary but the audience this was originally built for did not understand pivots and were typically using max as an aggregate.

Lets start with the code to create the stored procedure. This code should work in all versions of SSMS 2005 and above but I have not tested it in 2005 or 2016 but I can not see why it would not work.

create PROCEDURE [dbo].[USP_DYNAMIC_PIVOT]
    (
        @STATIC_COLUMN VARCHAR(255),
        @PIVOT_COLUMN VARCHAR(255),
        @VALUE_COLUMN VARCHAR(255),
        @TABLE VARCHAR(255),
        @AGGREGATE VARCHAR(20) = null
    )

AS


BEGIN

SET NOCOUNT ON;
declare @AVAIABLE_TO_PIVOT NVARCHAR(MAX),
        @SQLSTRING NVARCHAR(MAX),
        @PIVOT_SQL_STRING NVARCHAR(MAX),
        @TEMPVARCOLUMNS NVARCHAR(MAX),
        @TABLESQL NVARCHAR(MAX)

if isnull(@AGGREGATE,'') = '' 
    begin
        SET @AGGREGATE = 'MAX'
    end


 SET @PIVOT_SQL_STRING =    'SELECT top 1 STUFF((SELECT distinct '', '' + CAST(''[''+CONVERT(VARCHAR,'+ @PIVOT_COLUMN+')+'']''  AS VARCHAR(50)) [text()]
                            FROM '[email protected]+'
                            WHERE ISNULL('[email protected]_COLUMN+','''') <> ''''
                            FOR XML PATH(''''), TYPE)
                            .value(''.'',''NVARCHAR(MAX)''),1,2,'' '') as PIVOT_VALUES
                            from '[email protected]+' ma
                            ORDER BY ' + @PIVOT_COLUMN + ''

declare @TAB AS TABLE(COL NVARCHAR(MAX) )

INSERT INTO @TAB EXEC SP_EXECUTESQL  @PIVOT_SQL_STRING, @AVAIABLE_TO_PIVOT 

SET @AVAIABLE_TO_PIVOT = (SELECT * FROM @TAB)


SET @TEMPVARCOLUMNS = (SELECT replace(@AVAIABLE_TO_PIVOT,',',' nvarchar(255) null,') + ' nvarchar(255) null')


SET @SQLSTRING = 'DECLARE @RETURN_TABLE TABLE ('[email protected]_COLUMN+' NVARCHAR(255) NULL,'[email protected]+')  
                    INSERT INTO @RETURN_TABLE('[email protected]_COLUMN+','[email protected]_TO_PIVOT+')

                    select * from (
                    SELECT ' + @STATIC_COLUMN + ' , ' + @PIVOT_COLUMN + ', ' + @VALUE_COLUMN + ' FROM '[email protected]+' ) a

                    PIVOT
                    (
                    '[email protected]+'('[email protected]_COLUMN+')
                    FOR '[email protected]_COLUMN+' IN ('[email protected]_TO_PIVOT+')
                    ) piv

                    SELECT * FROM @RETURN_TABLE'



EXEC SP_EXECUTESQL @SQLSTRING

END

Next we will get our data ready for the example. I have taken the data example from the accepted answer with the addition of a couple of data elements to use in this proof of concept to show the varied outputs of the aggregate change.

create table temp
(
    date datetime,
    category varchar(3),
    amount money
)

insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('1/1/2012', 'ABC', 2000.00) -- added
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'DEF', 1500.00) -- added
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 700.00)
insert into temp values ('2/10/2012', 'DEF', 800.00) -- addded
insert into temp values ('3/1/2012', 'ABC', 1100.00)

The following examples show the varied execution statements showing the varied aggregates as a simple example. I did not opt to change the static, pivot, and value columns to keep the example simple. You should be able to just copy and paste the code to start messing with it yourself

exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','sum'
exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','max'
exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','avg'
exec [dbo].[USP_DYNAMIC_PIVOT] 'date','category','amount','dbo.temp','min'

This execution returns the following data sets respectively.

enter image description here

@Przemyslaw Remin 2017-09-28 11:21:01

Good job! Can you please make an option of TVF instead of stored procedure. Would be convenient to select from such TVF.

@SFrejofsky 2017-09-28 19:36:01

Unfortunately not, to the best of my knowledge, because you cannot have a dynamic structure for a TVF. You have to have a static set of columns in a TVF.

@Arockia Nirmal 2017-02-27 15:29:17

The below code provides the results which replaces NULL to zero in the output.

Table creation and data insertion:

create table test_table
 (
 date nvarchar(10),
 category char(3),
 amount money
 )

 insert into test_table values ('1/1/2012','ABC',1000.00)
 insert into test_table values ('2/1/2012','DEF',500.00)
 insert into test_table values ('2/1/2012','GHI',800.00)
 insert into test_table values ('2/10/2012','DEF',700.00)
 insert into test_table values ('3/1/2012','ABC',1100.00)

Query to generate the exact results which also replaces NULL with zeros:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),
@PivotColumnNames AS NVARCHAR(MAX),
@PivotSelectColumnNames AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column
SELECT @PivotColumnNames= ISNULL(@PivotColumnNames + ',','')
+ QUOTENAME(category)
FROM (SELECT DISTINCT category FROM test_table) AS cat

--Get distinct values of the PIVOT Column with isnull
SELECT @PivotSelectColumnNames 
= ISNULL(@PivotSelectColumnNames + ',','')
+ 'ISNULL(' + QUOTENAME(category) + ', 0) AS '
+ QUOTENAME(category)
FROM (SELECT DISTINCT category FROM test_table) AS cat

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
N'SELECT date, ' + @PivotSelectColumnNames + '
FROM test_table
pivot(sum(amount) for category in (' + @PivotColumnNames + ')) as pvt';

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

OUTPUT :

enter image description here

@Taryn 2012-05-01 21:13:29

Dynamic SQL PIVOT:

create table temp
(
    date datetime,
    category varchar(3),
    amount money
)

insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 700.00)
insert into temp values ('3/1/2012', 'ABC', 1100.00)


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT date, ' + @cols + ' from 
            (
                select date
                    , amount
                    , category
                from temp
           ) x
            pivot 
            (
                 max(amount)
                for category in (' + @cols + ')
            ) p '


execute(@query)

drop table temp

Results:

Date                        ABC         DEF    GHI
2012-01-01 00:00:00.000     1000.00     NULL    NULL
2012-02-01 00:00:00.000     NULL        500.00  800.00
2012-02-10 00:00:00.000     NULL        700.00  NULL
2012-03-01 00:00:00.000     1100.00     NULL    NULL

@smoothdeveloper 2015-02-20 02:40:07

Dynamic Dynamic SQL PIVOT: gist.github.com/smoothdeveloper/6898062 :)

@The Red Pea 2015-10-02 20:08:05

So \@cols must be string-concatenated, right? We can't use sp_executesql and parameter-binding to interpolate \@cols in there? Even though we construct \@cols ourself, what if somehow it contained malicious SQL. Any additional mitigating steps I could take before concatenating it and executing it?

@Patrick Schomburg 2016-11-29 21:37:05

How would you sort the rows and columns on this?

@Taryn 2016-11-29 21:40:05

@PatrickSchomburg There are a variety of ways - if you wanted to sort the @cols then you could remove the DISTINCT and use GROUP BY and ORDER BY when you get the list of @cols.

@Patrick Schomburg 2016-11-29 21:42:18

I'll try that. What about the rows? I'm using a date as well, and it doesn't come out in order.

@Taryn 2016-11-29 21:44:07

Not sure what you mean by rows @PatrickSchomburg. You still should be able to use ORDER BY to sort the data.

@Patrick Schomburg 2016-11-29 21:48:49

Nevermind I was putting the order by in the wrong spot.

@akd 2016-11-30 10:46:09

I have the similar problem but Instead of Category I have the categoryId. Category name comes from a different table. So would that be possible to read the column header from another table?

@Taryn 2016-11-30 12:22:36

@akd If I understand the questions correctly, you should be able to do this. You'd just have to join the tables in your PIVOT and query the other table when getting the @cols.

@akd 2016-11-30 12:28:10

Thanks but I am not sure where the join should go n the query?

@Taryn 2016-11-30 12:30:30

@akd Before trying to write it as a dynamic query, write it as a hard-coded version so you get the joins correct, then convert to dynamic. That's going to be the easiest way to proceed. Otherwise, you'll have to ask a new question about it, because it's going to be too difficult to try an debug in the comments.

@Enissay 2017-04-22 09:46:09

Same here as @akd, I have a huge join block and a huge where block. Normally they are identical for both queries, the question is where am I supposed to put them ? Can they be put in one place only and used by both for easy editing of conditions later ?

@Taryn 2017-04-22 13:03:37

@Enissay my suggestion would be to write the query as a static/hard coded version first, then convert it to dynamic sql. This allows you to get the logic and the result you want before diving into dynamic sql.

@Enissay 2017-04-22 14:12:21

The dynamic query is working fine now. Do you have any elegant way to edit the WHERE clause only once so that it's applied to both queries ? (the goal is to edit at one place instead of two, given that the changes are identical)

@smoothumut 2017-12-23 08:24:20

such a clean and simple code to understand, thanks a lot

@RaRdEvA 2019-01-25 18:05:45

This works perfectly. I just would like to learn what all this sentence means: STUFF((SELECT distinct ',' + QUOTENAME(c.category) FROM temp c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') Especially the part "FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)')"

@mkdave99 2016-07-21 13:18:07

Dynamic SQL PIVOT

Different approach for creating columns string

create table #temp
(
    date datetime,
    category varchar(3),
    amount money
)

insert into #temp values ('1/1/2012', 'ABC', 1000.00)
insert into #temp values ('2/1/2012', 'DEF', 500.00)
insert into #temp values ('2/1/2012', 'GHI', 800.00)
insert into #temp values ('2/10/2012', 'DEF', 700.00)
insert into #temp values ('3/1/2012', 'ABC', 1100.00)

DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';

SELECT @cols = @cols + QUOTENAME(category) + ',' FROM (select distinct category from #temp ) as tmp
select @cols = substring(@cols, 0, len(@cols)) --trim "," at end

set @query = 
'SELECT * from 
(
    select date, amount, category from #temp
) src
pivot 
(
    max(amount) for category in (' + @cols + ')
) piv'

execute(@query)
drop table #temp

Result

date                    ABC     DEF     GHI
2012-01-01 00:00:00.000 1000.00 NULL    NULL
2012-02-01 00:00:00.000 NULL    500.00  800.00
2012-02-10 00:00:00.000 NULL    700.00  NULL
2012-03-01 00:00:00.000 1100.00 NULL    NULL

@m0rg4n 2015-12-21 15:09:37

There's my solution cleaning up the unnecesary null values

DECLARE @cols AS NVARCHAR(MAX),
@maxcols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(CodigoFormaPago) 
                from PO_FormasPago
                order by CodigoFormaPago
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

select @maxcols = STUFF((SELECT ',MAX(' + QUOTENAME(CodigoFormaPago) + ') as ' + QUOTENAME(CodigoFormaPago)
                from PO_FormasPago
                order by CodigoFormaPago
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')

set @query = 'SELECT CodigoProducto, DenominacionProducto, ' + @maxcols + '
            FROM
            (
                SELECT 
                CodigoProducto, DenominacionProducto,
                ' + @cols + ' from 
                 (
                    SELECT 
                        p.CodigoProducto as CodigoProducto,
                        p.DenominacionProducto as DenominacionProducto,
                        fpp.CantidadCuotas as CantidadCuotas,
                        fpp.IdFormaPago as IdFormaPago,
                        fp.CodigoFormaPago as CodigoFormaPago
                    FROM
                        PR_Producto p
                        LEFT JOIN PR_FormasPagoProducto fpp
                            ON fpp.IdProducto = p.IdProducto
                        LEFT JOIN PO_FormasPago fp
                            ON fpp.IdFormaPago = fp.IdFormaPago
                ) xp
                pivot 
                (
                    MAX(CantidadCuotas)
                    for CodigoFormaPago in (' + @cols + ')
                ) p 
            )  xx 
            GROUP BY CodigoProducto, DenominacionProducto'

t @query;

execute(@query);

@davids 2012-05-01 21:11:07

You can achieve this using dynamic TSQL (remember to use QUOTENAME to avoid SQL injection attacks):

Pivots with Dynamic Columns in SQL Server 2005

SQL Server - Dynamic PIVOT Table - SQL Injection

Obligatory reference to The Curse and Blessings of Dynamic SQL

@Aaron Bertrand 2012-05-01 21:13:01

FWIW QUOTENAME only helps SQL injection attacks if you are accepting @tableName as a parameter from a user, and appending it to a query like SET @sql = 'SELECT * FROM ' + @tableName;. You can build plenty of vulnerable dynamic SQL strings and QUOTENAME won't do a lick to help you.

@Kermit 2014-04-01 16:17:54

@davids Please refer to this meta discussion. If you remove the hyperlinks, your answer is incomplete.

@davids 2014-04-29 18:04:07

@Kermit, I agree that showing the code is more helpful, but are you saying it is required in order for it to be an answer? Without the links, my reply is "You can achieve this using dynamic TSQL". The selected answer suggests the same route, with the added benefit if also showing how to do it, which is why it was selected as the answer.

@davids 2014-04-29 18:06:06

I up-voted the selected answer (prior to it being selected) because it had an example and will better help someone new. However, I think someone new should also read the links I provided, which is why I didn't remove them.

Related Questions

Sponsored Content

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

24 Answered Questions

[SOLVED] How do I perform an IF...THEN in an SQL SELECT?

24 Answered Questions

[SOLVED] Find all tables containing column with specified name - MS SQL Server

44 Answered Questions

16 Answered Questions

[SOLVED] LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

32 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

38 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

24 Answered Questions

37 Answered Questions

Sponsored Content