How to transpose from Row to Columns with unlimited number of rows?

Question

Post reply

How to transpose from Row to Columns with unlimited number of rows?

Vikka

Hall of Fame

Points: 3715
More actions
June 24, 2009 at 2:48 pm

#207468

Is there MS SQL analog of group_concat?
SELECT ClassID, group_concat(StudentID, '|')
FROM class
GROUP BY classID
ORDER BY classID;
Table: Class
ClassID StudentID
1 1
1 2
1 3
2 1
2 2
.
.
777 7
777 8
unlimited number of classes/students
How to convert it to a different table:
Classes
ClassID StudentList
1 1|2|3
2 1|2
.....................
777 7|8

Viewing 4 posts - 1 through 3 (of 3 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

CREATE TABLE #Class (row_id INT IDENTITY PRIMARY KEY, class_id INT NOT NULL, student_id INT NOT NULL);

CREATE TABLE #Classes (class_id INT PRIMARY KEY, student_list VARCHAR(8000) NOT NULL);

-- Test Data

INSERT#Class (class_id, student_id) VALUES (1, 1);

INSERT#Class (class_id, student_id) VALUES (1, 2);

INSERT#Class (class_id, student_id) VALUES (1, 3);

INSERT#Class (class_id, student_id) VALUES (2, 1);

INSERT#Class (class_id, student_id) VALUES (2, 2);

INSERT#Class (class_id, student_id) VALUES (3, 2);

INSERT#Class (class_id, student_id) VALUES (3, 1);

INSERT#Class (class_id, student_id) VALUES (3, 4);

INSERT#Class (class_id, student_id) VALUES (3, 5);

-- Do the concatenation

INSERT#Classes (class_id, student_list)

SELECTC1.class_id,

(

SELECTCONVERT(VARCHAR(10), student_id) + '|'

FROM#Class C2

WHEREC2.class_id = C1.class_id

ORDERBY student_id

FOR XML PATH('')

) AS student_list

FROM#Class C1

GROUPBY

C1.class_id;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Vikka Hall of Fame Points: 3715 More actions · Answer 2

Hi Paul,

thank you for your help. I just solved it too 😛

Declare @tmp_student TABLE (ClassID int, StudentID int )

Insert into @tmp__student (ClassID, StudentID) ...

select ClassID, StudentIDs from @tmp_student AS A CROSS APPLY

(SELECT '|' + Cast(StudentID as varchar) FROM @tmp_student AS B WHERE A.ClassID = B.ClassID FOR XML PATH('')) D (StudentIDs) GROUP BY ClassID, StudentIDs

Paul White SSC Guru Points: 150467 More actions · Answer 3

Cool.

Though the two queries are not exactly the same - check the query plans for both 😉

Also, I assumed you wanted the student ids concatenated in ascending order - you can remove the order by if ordering is not important to you.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi