-- Create table
CREATE TABLE #Department (Department VARCHAR(100))
-- Populate table
INSERT INTO #Department (Department)
SELECT 'ICT'
UNION ALL
SELECT 'CSE'
UNION ALL
SELECT 'ESRM'
UNION ALL
SELECT 'CPS'
UNION ALL
SELECT 'TEX'
UNION ALL
SELECT 'BBA'
-- Create table
CREATE TABLE #Person (ID NVARCHAR(100),PersonName VARCHAR(100))
-- Populate table
INSERT INTO #Person (ID,PersonName)
SELECT '0','Zakir'
UNION ALL
SELECT '1','DILIP'
UNION ALL
SELECT '2','ARUN'
UNION ALL
SELECT '3','KARIM'
UNION ALL
SELECT '4','SUJAN'
UNION ALL
SELECT '5','RAHIM'
-- Create table
CREATE TABLE #Student (Department NVARCHAR(100),ID VARCHAR(100))
-- Populate table
INSERT INTO #Student (Department,ID)
SELECT 'ICT','0'
UNION ALL
SELECT 'ICT','1'
UNION ALL
SELECT 'ESRM','2'
UNION ALL
SELECT 'ESRM','3'
UNION ALL
SELECT 'TEX','4'
UNION ALL
SELECT 'BBA','5'
-- Check orginal data
SELECT *
FROM #Department
SELECT * FROM #Person
SELECT *
FROM #Student
--- the select query ---------
SET ARITHABORT ON; -- must write
SELECT d.Department,STUFF(( SELECT '<br/>' + CAST((SELECT p.PersonName FROM #Person p WHERE p.ID = s.ID) AS NVARCHAR(MAX))
-- Add a comma (,) before each value
FROM #Student s
WHERE
s.Department= d.Department
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 5, '') AS [StudentName]
FROM #Department d
GROUP BY d.Department
------------------------------------------------------
-- Clean up
DROP TABLE #Person
DROP TABLE #Department
DROP TABLE #Student
CREATE TABLE #Department (Department VARCHAR(100))
-- Populate table
INSERT INTO #Department (Department)
SELECT 'ICT'
UNION ALL
SELECT 'CSE'
UNION ALL
SELECT 'ESRM'
UNION ALL
SELECT 'CPS'
UNION ALL
SELECT 'TEX'
UNION ALL
SELECT 'BBA'
-- Create table
CREATE TABLE #Person (ID NVARCHAR(100),PersonName VARCHAR(100))
-- Populate table
INSERT INTO #Person (ID,PersonName)
SELECT '0','Zakir'
UNION ALL
SELECT '1','DILIP'
UNION ALL
SELECT '2','ARUN'
UNION ALL
SELECT '3','KARIM'
UNION ALL
SELECT '4','SUJAN'
UNION ALL
SELECT '5','RAHIM'
-- Create table
CREATE TABLE #Student (Department NVARCHAR(100),ID VARCHAR(100))
-- Populate table
INSERT INTO #Student (Department,ID)
SELECT 'ICT','0'
UNION ALL
SELECT 'ICT','1'
UNION ALL
SELECT 'ESRM','2'
UNION ALL
SELECT 'ESRM','3'
UNION ALL
SELECT 'TEX','4'
UNION ALL
SELECT 'BBA','5'
-- Check orginal data
SELECT *
FROM #Department
SELECT * FROM #Person
SELECT *
FROM #Student
--- the select query ---------
SET ARITHABORT ON; -- must write
SELECT d.Department,STUFF(( SELECT '<br/>' + CAST((SELECT p.PersonName FROM #Person p WHERE p.ID = s.ID) AS NVARCHAR(MAX))
-- Add a comma (,) before each value
FROM #Student s
WHERE
s.Department= d.Department
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 5, '') AS [StudentName]
FROM #Department d
GROUP BY d.Department
------------------------------------------------------
-- Clean up
DROP TABLE #Person
DROP TABLE #Department
DROP TABLE #Student
No comments:
Post a Comment