August 21, 2014 at 3:35 pm
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
August 21, 2014 at 4:17 pm
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?
August 21, 2014 at 4:37 pm
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.
August 21, 2014 at 5:19 pm
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/
August 22, 2014 at 10:15 am
Correct me if I am wrong but that example is doing a pivot on a single field. I am looking to pivot 3 fields.
August 22, 2014 at 10:23 am
You're correct, but the principle is the same.
August 22, 2014 at 10:24 am
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/
August 22, 2014 at 10:39 am
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/
August 22, 2014 at 11:58 am
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!
September 5, 2014 at 12:58 pm
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)
September 5, 2014 at 3:16 pm
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
September 5, 2014 at 3:47 pm
Thanks Luis!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply