pivot 3 columns

  • I've used PIVOT before but it was for a single column. How do I pivot 3 columns while concatenating the 3 column headers into one? Here is my dataset....

    create table test

    (flsa_status varchar(30),

    bu_dept varchar(30),

    reg_temp varchar(5),

    ftpt char(2))

    insert into test values ('Exempt Salaried','Corp','Reg','FT',43);

    insert into test values ('Exempt Salaried','Corp','Reg','PT',10);

    insert into test values ('Exempt Salaried','Corp','Temp','FT',12);

    insert into test values ('Exempt Salaried','Corp','Temp','PT',2);

    Here is what I want the results to be. I dont know how to paste the results into seperate cells so I am using the Pipe as the seperator.....

    FLSA Status|Corp Reg FT|Corp Reg PT|Corp Temp FT|Corp Temp PT

    Exempt Salaried|43|10|12| 2

  • Something like this?

    create table test

    (flsa_status varchar(30),

    bu_dept varchar(30),

    reg_temp varchar(5),

    ftpt char(2),

    value int)

    insert into test values ('Exempt Salaried','Corp','Reg','FT',43);

    insert into test values ('Exempt Salaried','Corp','Reg','PT',10);

    insert into test values ('Exempt Salaried','Corp','Temp','FT',12);

    insert into test values ('Exempt Salaried','Corp','Temp','PT',2);

    SELECT flsa_status,

    MAX( CASE WHEN bu_dept = 'Corp' AND reg_temp = 'Reg' AND ftpt = 'FT' THEN value END) AS [Corp Reg FT],

    MAX( CASE WHEN bu_dept = 'Corp' AND reg_temp = 'Reg' AND ftpt = 'PT' THEN value END) AS [Corp Reg PT],

    MAX( CASE WHEN bu_dept = 'Corp' AND reg_temp = 'Temp' AND ftpt = 'FT' THEN value END) AS [Corp Temp FT],

    MAX( CASE WHEN bu_dept = 'Corp' AND reg_temp = 'Temp' AND ftpt = 'PT' THEN value END) AS [Corp Temp PT]

    FROM test

    GROUP BY flsa_status

    Would you need to do it dynamic?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • That would work if my dataset was only those 4 records. In reality it's alot more and scripting a CASE for each one is not ideal. Also, if a new BU_DEPT value is added, I would have to remember to add that to the script.

  • That's when a dynamic query comes handy. I don't have time right now, but this article should help you to get there based on the static sample I posted: http://www.sqlservercentral.com/articles/Crosstab/65048/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Correct me if I am wrong but that example is doing a pivot on a single field. I am looking to pivot 3 fields.

  • You're correct, but the principle is the same.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • is250sp (8/22/2014)


    Correct me if I am wrong but that example is doing a pivot on a single field. I am looking to pivot 3 fields.

    Take a look at the article he suggested about doing this as a dynamic cross tab. You aren't really doing 3 columns, you are concatenating the values from 3 columns. This looks like a fun challenge. If I get some time later today I will try to work something up.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Something as simple as this:

    create table test

    (flsa_status varchar(30),

    bu_dept varchar(30),

    reg_temp varchar(5),

    ftpt char(2),

    value int)

    insert into test values ('Exempt Salaried','Corp','Reg','FT',43);

    insert into test values ('Exempt Salaried','Corp','Reg','PT',10);

    insert into test values ('Exempt Salaried','Corp','Temp','FT',12);

    insert into test values ('Exempt Salaried','Corp','Temp','PT',2);

    insert into test values ('Additional Example','Corp','Reg','FT',143);

    insert into test values ('Additional Example','Corp','Reg','PT',110);

    insert into test values ('Additional Example','Corp','Temp','FT',112);

    insert into test values ('Additional Example','Corp','Temp','PT',21);

    DECLARE @SQL varchar(max)

    SELECT @SQL = 'SELECT flsa_status ' + CHAR(13)

    + (SELECT DISTINCT ' ,MAX( CASE WHEN bu_dept = ''' + bu_dept

    + ''' AND reg_temp = ''' + reg_temp

    + ''' AND ftpt = ''' + ftpt

    + ''' THEN value END) AS [' + bu_dept + ' ' + reg_temp + ' ' + ftpt + ']' + CHAR(13)

    FROM test

    FOR XML PATH(''),TYPE).value('.', 'varchar(max)')

    + 'FROM test ' + CHAR(13)

    + 'GROUP BY flsa_status'

    PRINT @SQL

    EXEC( @SQL)

    GO

    DROP TABLE test

    By the way, to concatenate the values I used the technique explained in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (8/22/2014)


    Something as simple as this:

    create table test

    (flsa_status varchar(30),

    bu_dept varchar(30),

    reg_temp varchar(5),

    ftpt char(2),

    value int)

    insert into test values ('Exempt Salaried','Corp','Reg','FT',43);

    insert into test values ('Exempt Salaried','Corp','Reg','PT',10);

    insert into test values ('Exempt Salaried','Corp','Temp','FT',12);

    insert into test values ('Exempt Salaried','Corp','Temp','PT',2);

    insert into test values ('Additional Example','Corp','Reg','FT',143);

    insert into test values ('Additional Example','Corp','Reg','PT',110);

    insert into test values ('Additional Example','Corp','Temp','FT',112);

    insert into test values ('Additional Example','Corp','Temp','PT',21);

    DECLARE @SQL varchar(max)

    SELECT @SQL = 'SELECT flsa_status ' + CHAR(13)

    + (SELECT DISTINCT ' ,MAX( CASE WHEN bu_dept = ''' + bu_dept

    + ''' AND reg_temp = ''' + reg_temp

    + ''' AND ftpt = ''' + ftpt

    + ''' THEN value END) AS [' + bu_dept + ' ' + reg_temp + ' ' + ftpt + ']' + CHAR(13)

    FROM test

    FOR XML PATH(''),TYPE).value('.', 'varchar(max)')

    + 'FROM test ' + CHAR(13)

    + 'GROUP BY flsa_status'

    PRINT @SQL

    EXEC( @SQL)

    GO

    DROP TABLE test

    By the way, to concatenate the values I used the technique explained in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/

    Wow this is awesome. Now I will need to dissect it. Thanks!

  • Using this example, how do I UNION it to another similar script? If not is there a way to write the output to a temp table?

    DECLARE @SQL varchar(max)

    SELECT @SQL = 'SELECT flsa_status ' + CHAR(13)

    + (SELECT DISTINCT ' ,MAX( CASE WHEN bu_dept = ''' + bu_dept

    + ''' AND reg_temp = ''' + reg_temp

    + ''' AND ftpt = ''' + ftpt

    + ''' THEN value END) AS [' + bu_dept + ' ' + reg_temp + ' ' + ftpt + ']' + CHAR(13)

    FROM test

    FOR XML PATH(''),TYPE).value('.', 'varchar(max)')

    + 'FROM test ' + CHAR(13)

    + 'GROUP BY flsa_status'

    PRINT @SQL

    EXEC( @SQL)

  • You could do it in the same EXEC() statement. The problem is that you need to be sure that you're generating the same columns or you'll get an error.

    Here's an example:

    DECLARE @SQL varchar(max),

    @SQL2 varchar(max)

    SELECT @SQL = 'SELECT flsa_status ' + CHAR(13)

    + (SELECT DISTINCT ' ,MAX( CASE WHEN bu_dept = ''' + bu_dept

    + ''' AND reg_temp = ''' + reg_temp

    + ''' AND ftpt = ''' + ftpt

    + ''' THEN value END) AS [' + bu_dept + ' ' + reg_temp + ' ' + ftpt + ']' + CHAR(13)

    FROM test

    FOR XML PATH(''),TYPE).value('.', 'varchar(max)')

    + 'FROM test ' + CHAR(13)

    + 'GROUP BY flsa_status'

    PRINT @SQL

    SELECT @SQL2 = 'SELECT flsa_status ' + CHAR(13)

    + (SELECT DISTINCT ' ,MAX( CASE WHEN bu_dept = ''' + bu_dept

    + ''' AND reg_temp = ''' + reg_temp

    + ''' AND ftpt = ''' + ftpt

    + ''' THEN value END) AS [' + bu_dept + ' ' + reg_temp + ' ' + ftpt + ']' + CHAR(13)

    FROM test

    FOR XML PATH(''),TYPE).value('.', 'varchar(max)')

    + 'FROM test ' + CHAR(13)

    + 'GROUP BY flsa_status'

    PRINT @SQL2

    EXEC( @SQL + ' UNION ALL ' + @SQL2)

    EDIT: Missed a space before the union all

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks Luis!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply