CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
markA
markA

Reputation: 1609

SQL Server 2008 Parse Unknown Number of Entries

I do not know how many courses each person will have or what the names of the courses are due to the number of possibilities so I can't use a normal sub-select or case.

I have a table like this:

ID    NAME     CLASS
----------------------
1     BOB      Course1
1     BOB      Course2
1     BOB      Course3
2     JOHN     Course1
2     JOHN     Course2
3     SAM      Course1
4     BILL     Course2
4     BILL     Course3

I want to make it look like this where the last column is for whoever has the most classes:

ID    NAME     CLASS     CLASS2     CLASS3     CLASS4..........
------------------------------------------------------
1     BOB      Course1   Course2    Course3    NULL............
2     JOHN     Course1   Course2    NULL       NULL............
3     SAM      Course1   NULL       NULL       NULL............
4     BILL     Course2   Couse3     NULL       NULL............

The best idea I had was to keep getting the max CLASS and deleting it until I ran out of records.

edit: To Clarify: My column headers will not be actual courses just a way to number how many courses a teacher is assigned to.

Upvotes: 0

Views: 103

Answers (2)

Hogan
Hogan

Reputation: 70523

Abe's suggestion looks like this:

 select * from Class
 pivot (COUNT(ID) for CLASS in ([Course1],[Course2],[Course3])) as Taking

Which gives results like this:

Name    Course1 Course2 Course3
BILL    0       1       1
BOB     1       1       0       
JOHN    1       1       0
SAM     1       0       0

Of course this is slightly different than what you want... you want course 1 to have a value for the "first course." The only way to do this is with dynamic SQL.

If you want a comma separated list of courses (in one column) that is much easier and faster to generate.

Comma list

select c.Name, 
       STUFF((SELECT ( ', ' + c2.Class )
               FROM #Class c2
               WHERE c.Name = c2.Name
               ORDER BY c2.Class 
               FOR XML PATH( '' )),1,2,'') as [Class List]
FROM #Class c 
GROUP BY c.Name

Gives you:

Name   Class List
BILL   Course2, Course3
BOB    Course1, Course2, Course3
JOHN   Course1, Course2
SAM    Course1

Upvotes: 3

Abe Miessler
Abe Miessler

Reputation: 85056

You can look at using a PIVOT

Read more about it here:

http://msdn.microsoft.com/en-us/library/ms177410.aspx

You can use MAX on ID as your aggregate function I believe.

Upvotes: 2

Related Questions