pivot 3 columns

Question

Post reply

pivot 3 columns

is250sp

SSCarpal Tunnel

Points: 4361
More actions
August 21, 2014 at 3:35 pm

#151370

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

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

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

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

is250sp SSCarpal Tunnel Points: 4361 More actions · Answer 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.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

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

is250sp SSCarpal Tunnel Points: 4361 More actions · Answer 4

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

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/

Luis Cazares SSC Guru Points: 183698 More actions · Answer 7

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

is250sp SSCarpal Tunnel Points: 4361 More actions · Answer 8

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!

is250sp SSCarpal Tunnel Points: 4361 More actions · Answer 9

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)

Luis Cazares SSC Guru Points: 183698 More actions · Answer 10

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

is250sp SSCarpal Tunnel Points: 4361 More actions · Answer 11

is250sp

SSCarpal Tunnel

Points: 4361

September 5, 2014 at 3:47 pm

#1743631

Thanks Luis!