Member 11322120 Ответов: 1

Как поместить все данные в один столбец второй таблицы в внутренне Соединенные в один столбец первой таблицы

CREATE TABLE #tempStudent
(
	Student_Id int,
	Name nvarchar(10),
	Class nvarchar(10)
)
Insert Into #tempStudent Values(1,'Joy', 'C1'),
(2,'Root', 'C2'),
(3,'Devil', 'C3')
CREATE TABLE #tempSubject
(
	Student_Id int,
	SubjectName nvarchar(10)
)
Insert Into #tempSubject Values
(1,'English'),
(1,'Math'),
(2,'Science'),
(2,'Math'),
(3,'Science'),
(3,'English')


Хотите получить результат как:
Name	Class	SubjectName
Joy		C1		English,Math
Root	C2		Science,Math
Devil	C3		Science,English


Что я уже пробовал:

select st.Name, st.Class, sb.SubjectName from #tempSubject as sb
inner join #tempStudent as st on sb.Student_Id=st.Student_Id


текущий результат
Name	Class	SubjectName
Joy		C1		English
Joy		C1		Math
Root	C2		Science
Root	C2		Math
Devil	C3		Science
Devil	C3		English

1 Ответов

Рейтинг:
4

Maciej Los

Есть много способов достичь этого...

1) Использование КТОС[^].
Прежде чем я покажу вам, как его использовать, я должен упомянуть, что ваша структура базы данных неверна. Вам нужна "промежуточная таблица", которая свяжет предметы со студентами. Видеть:

DECLARE @tempStudent TABLE(	Student_Id int, [Name] nvarchar(10), Class nvarchar(10))
Insert Into @tempStudent Values(1,'Joy', 'C1'),(2,'Root', 'C2'),(3,'Devil', 'C3')
DECLARE @tempSubject TABLE( Subject_Id int, SubjectName nvarchar(10))
Insert Into @tempSubject Values(1,'English'),(2,'Math'),(3,'Science')
--new table with relationship many (Student_id) to many (Subject_id)
DECLARE @tempSubjectStudent TABLE( Student_Id int, Subject_Id int)
Insert Into @tempSubjectStudent Values(1, 1),(1,2),(2,3),(2,2),(3,3),(3,1)


Теперь пример запроса CTE:
;WITH CTE AS
(
	--initial query
	SELECT DISTINCT st.Student_Id, st.[Name], st.Class, CONVERT(NVARCHAR(MAX), '') AS SubjectName, 
		0 AS RowNo, 0 AS Subject_Id , COUNT(*) OVER(PARTITION BY st.Student_Id) AS NoOfSubjects
	FROM @tempStudent st INNER JOIN @tempSubjectStudent ss ON st.Student_Id = ss.Student_Id 
	--recursive part
	UNION ALL
	SELECT st.Student_Id, st.[Name], st.Class, CONCAT(st.SubjectName, su.SubjectName, ', ') AS SubjectName,
		st.RowNo + 1 AS RowNo, ss.Subject_Id AS Subject_Id ,st.NoOfSubjects
	FROM CTE st
		INNER JOIN @tempSubjectStudent ss ON st.Student_Id = ss.Student_Id  
		INNER JOIN @tempSubject su ON ss.Subject_Id = su.Subject_Id
	WHERE st.RowNo<= st.NoOfSubjects AND ss.Subject_Id > st.Subject_Id
)
SELECT  [Name], Class, SubjectName
FROM CTE 
WHERE RowNo=NoOfSubjects


Результат:
Name	Class	SubjectName
Devil	C3	English, Science, 
Root	C2	Math, Science, 
Joy		C1	English, Math,


Для получения более подробной информации, пожалуйста, смотрите:
With обобщенное_табличное_выражение (Transact-SQL) при SQL-сервера Майкрософт документы[^]
Обобщенные табличные выражения (введение в КТР-х) - необходимые для SQL[^]
С помощью рекурсивного CTE-выражения для формирования списка – SQLServerCentral[^]

2) Использование для XML [^]
Видеть:
Как объединить текст из нескольких строк в одну текстовую строку в SQL server? - переполнение стека[^]
Примеры: использование режима PATH - SQL Server | Microsoft Docs[^]
Как Stuff и 'For Xml Path' работают в Sql Server - Stack Overflow[^]