Multiple CSV Column to Row SQL Query

Question

I have following data

I want to Convert Column to Row

I have use following 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)

It give me following Result

but I want to seprate Subjects, Hoursperweek, days, time. How can I do that ?

ApplicantID  Subjects  HoursPerWeek   Days       Time
1             a          12           Sun      12:45:12
1             b          25           Mon      14:45:12

Answer 1

Here is a function to split the Comma separated values into Rows

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 t.N + 1
               FROM   cteTally t
               WHERE  ( Substring(@List, t.N, 1) = @Delimiter
                         OR t.N = 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 

Now the ugly code to split the data

;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) 

Result:

╔═════════════╦══════════╦══════════════╦══════╦══════════╗
║ APPLICANTID ║ SUBJECTS ║ HOURSPERWEEK ║ DAYS ║   TIME   ║
╠═════════════╬══════════╬══════════════╬══════╬══════════╣
║           1 ║ a        ║           12 ║ sun  ║ 12:45:12 ║
║           1 ║ b        ║           25 ║ mon  ║ 14:45:12 ║
╚═════════════╩══════════╩══════════════╩══════╩══════════╝

This is the reason we should never store comma separated values into single column. When you want to parse the data things get difficult and we need to such ugly codes to parse the data