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!