CODEWITHSUNDEEP
sql-servert-sqlpivotdynamic-sql
dcrowley01
dcrowley01

Reputation: 151

Dynamic SQL Pivot: Create new columns based on column value and parse column names

Using the code below:

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

I get a result like:

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

However, my column values naming convention vary.For example, my dataset could look like:

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

insert into temp values ('1/1/2012', 'ABC-CO', 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-PR', 1100.00)

'-CO' and '-PR' can appear after any value, not just 'ABC'. What I need to do though, is drop '-%' from any column header when it appears in order to create the same result table as above. Does anyone know to go about doing this?

Upvotes: 1

Views: 86

Answers (1)

basic
basic

Reputation: 11968

Note that 'ABC-CO' cannot be stored in varchar (3) field - you need varchar (6).

If '-CO' and '-PR' are the only postfixes, you can use the REPLACE function:

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

insert into temp values ('1/1/2012', 'ABC-CO', 1000.00)
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'GHI-PR', 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(REPLACE(REPLACE(c.category, '-PR',''),'-CO',''))
        FROM temp c
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

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


execute(@query)

drop table temp

Upvotes: 1

Related Questions