Show multiple records in a row in SQL Server

Question

I have posted a question here Show multiple records in a row to get a result like this

It was answered perfectly in MySQL but I'm working with SQL Server where the query is different. My tables are

CREATE TABLE events
(
    id int NOT NULL,
    event_name varchar(100) NOT NULL,
    event_id int NOT NULL
);

INSERT INTO events (id, event_name, event_id)
VALUES (1, 'dance', 1), (2, 'sing', 2), (3, 'acting', 3), (4, 'debate', 4);

CREATE TABLE students 
(
    id int NOT NULL,
    student_name text NOT NULL,
    event_id int NOT NULL
);

INSERT INTO students (id, student_name, event_id)
VALUES (1, 'student1', 1), (2, 'student1', 2),
       (3, 'student1', 3), (4, 'student1', 4),
       (5, 'student2', 3), (6, 'student3', 2),
       (7, 'student3', 4);

I was trying with a sample query here http://rextester.com/RXK72524, but it's not working as it.

The query I used

SELECT 
    s.student_name,
    STUFF (g.student_name, 1, 1, '') AS events
FROM   
    Students s
CROSS APPLY
    (SELECT 
         ',' + e.event_name
     FROM  
         students s
     LEFT JOIN 
         events e ON s.event_id = e.event_id
     ORDER BY 
         e.event_name 
     FOR XML PATH ('')) g (student_name)

Please correct me. Thanks

Your life will be easier if you make student_name column as `NVACHAR` instead `TEXT` — Alvaro Silvino, Dec 25 '17 at 20:02

Alvaro Silvino · Accepted Answer · 2017-12-25T20:05:04.253

http://rextester.com/LOQZE28952

PS: Your life will be easier if you make student_name column as NVACHAR instead TEXT,

It will save you to not use the cast function

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

select @@version as 'sql server version'


CREATE TABLE events (
    id int NOT NULL,
    event_name varchar(100) NOT NULL,
    event_id int NOT NULL
);

INSERT INTO events (id, event_name, event_id)
VALUES
    (1, 'dance', 1),
    (2, 'sing', 2),
    (3, 'acting', 3),
    (4, 'debate', 4);


CREATE TABLE students (
  id int NOT NULL,
  student_name text NOT NULL,
  event_id int NOT NULL
);

INSERT INTO students (id, student_name, event_id)
VALUES
    (1, 'student1', 1),
    (2, 'student1', 2),
    (3, 'student1', 3),
    (4, 'student1', 4),
    (5, 'student2', 3),
    (6, 'student3', 2),
    (7, 'student3', 4);

SELECT DISTINCT(cast(s.student_name as NVARCHAR(100))) as Students,
       stuff (g.student_name, 1, 1, '') as events
FROM   Students s

CROSS APPLY
(
    SELECT ',' + e.event_name
    FROM  Students s1
    left join events e on s1.event_id = e.event_id 
    where cast(s1.student_name as NVARCHAR(100)) = cast(s.student_name as NVARCHAR(100))
    ORDER BY e.event_name 
    FOR XML PATH ('')
) g (student_name)

Show multiple records in a row in SQL Server

1 Answers1