Несколько столбцов CSV для строки SQL-запроса
У меня есть следующие данные
Я хочу преобразовать столбцы в строку
У меня есть использование Query ::
Select Distinct [ApplicantID], Split.a.value('.', 'VARCHAR(100)') AS Subjects, hoursperweek,[Days],[Time] From ( Select Cast ('<M>' + REPLACE([Subjects], ',', '</M><M>') + '</M>' AS XML) AS Subjects , ApplicantID, hoursperweek,[Days],[Time] From [dbo].[Progrm]) AS A CROSS APPLY Subjects.nodes ('/M') AS Split(a)
Это дает мне следующий результат
но я хочу сгруппировать Субъектов, Часов, дней, времени. Как я могу это сделать ?
ApplicantID Subjects HoursPerWeek Days Time 1 a 12 Sun 12:45:12 1 b 25 Mon 14:45:12
Вот функция разделения разделенных запятыми значений на строки
CREATE FUNCTION dbo.Splitstrings (@List NVARCHAR(MAX), @Delimiter NVARCHAR(255)) RETURNS @split_tab TABLE ( RN INT IDENTITY(1, 1), SPLIT_VAL VARCHAR(100)) WITH SCHEMABINDING AS BEGIN ; ;WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), E2(N) AS (SELECT 1 FROM E1 a,E1 b), E4(N) AS (SELECT 1 FROM E2 a, E2 b), E42(N) AS (SELECT 1 FROM E4 a, E2 b), cteTally(N) AS (SELECT 0 UNION ALL SELECT TOP (Datalength(Isnull(@List, 1))) Row_number()OVER ( ORDER BY (SELECT NULL)) FROM E42), cteStart(N1) AS (SELECT tN + 1 FROM cteTally t WHERE ( Substring(@List, tN, 1) = @Delimiter OR tN = 0 )) INSERT INTO @split_tab SELECT SPLIT_VAL= Substring(@LIST, S.N1, Isnull(NULLIF(Charindex(@DELIMITER, @LIST, S.N1), 0) - S.N1, 8000)) FROM CTESTART S; RETURN END
Теперь уродливый код для разделения данных
;WITH data AS (SELECT 1 ApplicantID, 'a,b' Subjects, '12,25' HoursPerWeek, 'sun,mon' Days, '12:45:12,14:45:12' Time), sub AS (SELECT RN, ApplicantID, Subjects=SPLIT_VAL FROM data CROSS apply Splitstrings (Subjects, ',')), HPW AS (SELECT RN, ApplicantID, HoursPerWeek=SPLIT_VAL FROM data CROSS apply Splitstrings (HoursPerWeek, ',')), Days AS (SELECT RN, ApplicantID, Days=SPLIT_VAL FROM data CROSS apply Splitstrings (Days, ',')), Time AS (SELECT RN, ApplicantID, Time=SPLIT_VAL FROM data CROSS apply Splitstrings (Time, ',')) SELECT D.APPLICANTID, S.SUBJECTS, H.HOURSPERWEEK, DA.DAYS, T.TIME FROM DATA D FULL OUTER JOIN SUB S ON D.APPLICANTID = S.APPLICANTID FULL OUTER JOIN HPW H ON D.APPLICANTID = H.APPLICANTID AND H.RN = S.RN FULL OUTER JOIN DAYS DA ON D.APPLICANTID = DA.APPLICANTID AND DA.RN = COALESCE(S.RN, H.RN) FULL OUTER JOIN TIME T ON D.APPLICANTID = T.APPLICANTID AND T.RN = COALESCE(S.RN, H.RN, DA.RN)
Результат:
╔═════════════╦══════════╦══════════════╦══════╦══════════╗ ║ APPLICANTID ║ SUBJECTS ║ HOURSPERWEEK ║ DAYS ║ TIME ║ ╠═════════════╬══════════╬══════════════╬══════╬══════════╣ ║ 1 ║ a ║ 12 ║ sun ║ 12:45:12 ║ ║ 1 ║ b ║ 25 ║ mon ║ 14:45:12 ║ ╚═════════════╩══════════╩══════════════╩══════╩══════════╝
По этой причине мы не должны хранить значения, разделенные запятыми, в один столбец. Когда вы хотите разобрать данные, все становится сложно, и нам нужны такие уродливые коды для анализа данных