Showing some data in rows as columns in SQL Server

Question

I have a query in SQL Server 2014 that returns a list of students that belong to more than one club. This data is retrieved from a relationship table listed below:

ID       Student      ClubName
==============================
1        Bob          Chess
2        Bob          Tennis
3        Frank        Soccer
4        Frank        Math
5        Tom          Chess
6        Tom          Math
7        Tom          Drama

I can list the number students that belong to more than one club using the following query.

SELECT Student, COUNT(ClubName)
FROM StudentClub
GROUP BY Student
Having COUNT(ClubName) > 1

Student   Number of Clubs
=========================
Bob           2
Frank         2
Tom           3

What I would like to do is list the Club Names each student belongs to in columns, like this ...

Student      Club1     Club2    Club3
=====================================
Bob          Chess     Tennis
Frank        Soccer    Math
Tom          Chess     Math     Drama

Is this possible to do in the same query that determines which students belong to more than one club?

P.S. I gave the example using names rather than IDs as I felt it would be easier to explain/read. In my real table I use Student IDs and Club IDs.

Look for dynamic pivot. You can find many similar questions here in SO. — Giorgos Betsos
– Giorgos Betsos, Commented Dec 8, 2015 at 16:45

buqing · Accepted Answer · 2015-12-08 16:59:12Z

1

If you some programming language, it is easy to list the result. First, you can create a HashMap, key the studentName/studentId, value is a list. You can put the clubName/clubId into the list. After you get the map, you can traverse the map and print out the result you want. Here is the pseudo code

new a hashMap>//key is the student Id, values is the club list
select studentid,clubId from the table and store it into resultset
traverse the resultset, put the proper value into the hashmap
traverse the hashmap and print out the vales for key:hashmap.keysets print key:hashmap.get(Key)

answered Dec 8, 2015 at 16:59

buqing

1,05510 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

webworm Over a year ago

could do as you suggest and manipulate to result set in code but ideally I would like to see if I could do this via a SQL script.

Collectives™ on Stack Overflow

Showing some data in rows as columns in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related