SQL Query to Convert Multiple CSV Column to Row

Suppose you have following kind of table that has multiple CSV and you want to convert it to Row.





Want Result like :







 


First Create Following Table- Valued Functions in SQL Server





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


Then Use Following Query (CTE) to Convert in to Row.



;WITH StudentSubjects

     AS
(
 
SELECT          StudentID,

                StudentName,

                Subjects,

                HoursPerWeek,

                Days,

                Time
FROM StudentClass 
 
),

SUBJECTS
 
AS
(
SELECT    RN, -- RN FROM SPITSTRING FUNCTION

          StudentID,

          StudentName,

          Subjects=SPLIT_VAL

FROM StudentClass

CROSS apply Splitstrings (Subjects, ',')),

 
 
HOURSPERWEEK
 
AS
(
SELECT
          RN,

          StudentID,

          StudentName,

          HoursPerWeek=SPLIT_VAL

FROM StudentClass

CROSS apply Splitstrings (HoursPerWeek, ',')),

Days

AS
(
SELECT
          RN,

          StudentID,

          StudentName,

          Days=SPLIT_VAL

FROM StudentClass

CROSS apply Splitstrings (Days, ',')),

Time

AS
(
 SELECT
           RN,

           StudentID,

           StudentName,

           Time=SPLIT_VAL

FROM StudentClass

CROSS apply Splitstrings (Time, ','))

SELECT
           D.STUDENTID,

           D.STUDENTNAME,

           RTRIM(LTRIM(S.SUBJECTS)) AS SUBJECTS,

           RTRIM(LTRIM(H.HOURSPERWEEK)) AS HOURSPERWEEK,

           RTRIM(LTRIM(DA.DAYS)) AS DAYS,

           RTRIM(LTRIM(T.TIME)) AS TIME

 
FROM StudentClass D

FULL OUTER JOIN SUBJECTS S

          ON D.StudentID = S.StudentID

FULL OUTER JOIN HOURSPERWEEK H

          ON D.StudentID = H.StudentID

          AND H.RN = S.RN

FULL OUTER JOIN DAYS DA

           ON D.StudentID = DA.StudentID

           AND DA.RN = COALESCE(S.RN, H.RN)

FULL OUTER JOIN TIME T

           ON D.StudentID = T.StudentID

           AND T.RN = COALESCE(S.RN, H.RN, DA.RN)



---------------------------------------------------------------------


SQL Create Table and Insert Value Script :


CREATE TABLE [dbo].[StudentClass](

[StudentID] [int] NULL,

[StudentName] [nvarchar](150) NULL,

[Subjects] [nvarchar](150) NULL,

[HoursPerWeek] [nvarchar](150) NULL,

[Days] [nvarchar](150) NULL,

[Time] [nvarchar](150) NULL

) ON [PRIMARY]




GO
 
 
INSERT [dbo].[StudentClass] ([StudentID], [StudentName], [Subjects], [HoursPerWeek], [Days], [Time]) VALUES (1, N'Ria S', N'Maths, Science', N'10,15', N'Sun, Mon', N'10:20, 11:50')




GO
 
 
INSERT [dbo].[StudentClass] ([StudentID], [StudentName], [Subjects], [HoursPerWeek], [Days], [Time]) VALUES (2, N'Robert T', N'Economics, Graphics, Accounting', N'15,18,16', N'Tue, Wed, Thur', N'10:60,13:20, 18::50')




GO