Pivot Data

Question

Post reply

Pivot Data

vani_r14

SSCrazy

Points: 2843
More actions
March 28, 2012 at 12:24 am

#96711

Hi All

I would like to get some help / advise on how to do below -

I have a table with all staff who have completed different training and some who have not. what I would like to do is create a table with all courses listed as a header and have one column with the employee no and relevant results (i.e. completed / not completed) under each of those courses. Course heading will remain static rest will be dynamic

I have managed to get the Pivoting done - to create the column headers and display the employee no's. What am unable to work out is how to get the result of each of thoses courses for each employee

E.g.

Emp No Course1 Course2 Course3 Course4 Course5

1 Pass NA Pass Cancelled Booked

2. Pass Pass Pass Pass Pass

.....

Can someone please help

thanks

Vani

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ant-Green SSC Guru Points: 112965 More actions · Answer 1

Ant-Green

SSC Guru

Points: 112965

March 28, 2012 at 1:32 am

#1465494

i would take a look at cross tabs, links below, and the pivot keyword.

CT-P1[/url]

CT-P2[/url]

also follow the second link in my signature to post DDL and sample data so that we can create a mock environment to help you out

vani_r14 SSCrazy Points: 2843 More actions · Answer 2

Hi Anthony

Thanks for your help... I am unable to get it working.



-- Create table

create table trainingData

(emp_id varchar(6),

course_id varchar(50),

result varchar(10))



-- Insert data

Insert into trainingData

Values ('1', 'Course1', 'Pass')

Insert into trainingData

Values ('1', 'Course2', 'Pass')

Insert into trainingData

Values ('1', 'Course3', 'Cancelled')

Insert into trainingData

Values ('1', 'Course4', 'Booked')

Insert into trainingData

Values ('2', 'Course1', 'Booked')

Insert into trainingData

Values ('2', 'Course2', 'Booked')

Insert into trainingData

Values ('2', 'Course3', 'Pass')

Insert into trainingData

Values ('2', 'Course4', 'Cancelled')



-- Final Extract I would like to Create  

Create table finalExtract

(emp_id varchar(6), Course1 varchar(10), Course2 Varchar(10), Course3 Varchar(10), Course4 Varchar(10))

-- Insert Data into finalExtract 

Insert into finalExtract

Values ('1', 'Pass', 'Pass', 'Cancelled', 'Booked')

Insert into finalExtract

Values ('2', 'Booked', 'Booked', 'Pass', 'Cancelled')

In the Final Extract So far I have managed to Create below using Pivot.



Create table finalExtract

(emp_id varchar(6), Course1 varchar(10), Course2 Varchar(10), Course3 Varchar(10), Course4 Varchar(10))

Insert into finalExtract

Values ('1')

Am unable to get the result column for the employee under the relevant course.

Any ideas would be very helpful. Can some one please help

Thanks

vani_r14 SSCrazy Points: 2843 More actions · Answer 3

[font="Tahoma"]Hi all

Thank you for your help. I have managed to work it out finally. I realised the major difference in this query is datatype of Data that am converting. Since it's Varchar and not Integer am unable to use the Aggregate functions.

Please find below is the links i used as reference -

http://ericfickes.com/2010/04/what-if-you-want-to-pivot-against-a-text-column/ [/url]

http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

http://stackoverflow.com/questions/428041/sql-server-2005-turn-columns-into-rows

Vani[/font]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

vani_r14 (3/28/2012)

[font="Tahoma"]Since it's Varchar and not Integer am unable to use the Aggregate functions.

Absolutely not true. Use MAX.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

vani_r14 SSCrazy Points: 2843 More actions · Answer 5

vani_r14

SSCrazy

Points: 2843

March 28, 2012 at 5:49 pm

#1466108

Hi Jeff

I tried both min and max did not work. anyway this worked, so i thought i will share the links.

Cheers

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 6

I guess what Jeff means is this:



SELECT emp_id,

       course1,

       course2,

       course3,

       course4

  FROM (SELECT emp_id, course_id, result

          FROM dbo.trainingData) TB

 PIVOT(MAX(result) FOR course_id IN ([course1], [course2], [course3], [course4])) X;

But for this to work effectively you must be sure that you have a unique constraint for "emp_id" and "course_id". Otherwise MAX() will return the biggest in order of characters which is not what you need. From the data you presented that will work.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA