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
