• 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!