SQL help pivoting a single table

Question

I have a table containing several columns. There are multiple records that contain the same info except for the last column which is different. I need this last column to be combined into one row as additional columns. There will only be up to 3 additional columns (course, course2, course3). Below is my table layout. Thanks for any help.

Columns
=============
EmployeeNumber  
Email   
LastName    
FirstName   
Address1    
City    
State   
Zip 
Phone   
Certified   
School  
EmployeeType    
BirthDate   
Course  
StaffNumber

Let me try a smaller example than my table...

StaffID  FName       LName      Course
=========================================
1111      John       Smith      History
1111      John       Smith      AP History
1111      John       Smith      Economics
2222     Jane       Smith       Science
2222     Jane       Smith       Chemistry
2222     Jane       Smith       Geology

I need it to read...

StaffID  FN       LN      Course1  Course2     Course3
=======================================================
1111    John     Smith    History  AP History  Economics
2222   Jane      Smith     Science   Chemistry   Geology

The only column that will contain different data that I need to combine is Course. Otherwise, there will be several rows with identical data that I need to combine into one.

Sorry, I said last column, but it's the next to last column containing different info. — Jeff Green, Jul 26 '18 at 17:11
Please post your table schema, some sample data as INSERT statements. and the desired output using your sample data — user1443098, Jul 26 '18 at 17:12
can you post some expexted results? How did you get course, course2, course3? — S3S, Jul 26 '18 at 17:12
I tried, and it looked terrible. I will see if I can clean it up. — Jeff Green, Jul 26 '18 at 17:32
Is there any inherent ordering here that would cause `history` then `ap history` then `economics` to go to `course1, course2, course3` respectively for `staffid` of `1111`? — JNevill, Jul 26 '18 at 17:43
Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Igor, Jul 26 '18 at 17:44
Indent your text tables with four spaces (or highlight the block and hit the `{}` button) so they are formatted properly. I have done that twice now, but you reverted the edit once. — JNevill, Jul 26 '18 at 17:45
Sorry about that. As far as any ordering, I can sort it any way needed. It doesn't matter what order they're in. — Jeff Green, Jul 26 '18 at 17:47
If SQL Server just had the `LISTAGG()` function, you'll be good. — The Impaler, Jul 26 '18 at 20:20

LukStorms · Accepted Answer · 2018-07-26T20:41:07.820

1

SELECT *
FROM
(
  SELECT
  EmployeeNumber,
  Email, LastName, FirstName,
  Address1, City, State,
  Zip, Phone,
  Certified,
  School,
  EmployeeType,
  BirthDate,
  StaffNumber,
  Course as CourseName,
  CONCAT('Course', row_number() over (partition by  EmployeeNumber order by Course)) AS Col
  FROM employee_courses
) src
PIVOT
(
   MAX(CourseName) 
   FOR Col IN ([Course1],[Course2],[Course3])
) pvt

edited Jul 26 '18 at 20:41

answered Jul 26 '18 at 18:02

LukStorms

28,916
5
31
45

I had to remove 'Course' before row_number() because it was giving a type conversion error, and reflect that in the FOR clause but it worked perfectly. Thanks! – Jeff Green Jul 26 '18 at 20:31
@JeffGreen Thx. I wrote that down without actaully testing it against sample data. But should be fine with using a CONCAT instead of +. Or indeed, with just numbers for column names. – LukStorms Jul 26 '18 at 20:43

SQL help pivoting a single table

1 Answers1