Duplicate and Split Row based on value by using SQ

Question

I have set of data and wants to split multiple rows based on the column value.

For Example,

Source Data:

Expected Output:

Thanks, Lawrance A

Answer 1

The Best and Simple way to solve above is

Select value as UID, Name ,Age ,Education ,Department

from StackSoln

Cross apply string_split(UID,',')

STRING_SPLIT

A table-valued function that splits a string into rows of substrings, based on a specified separator character.

CROSS APPLY

returns only rows from the outer table that produce a result set from the table-valued function. It other words, result of CROSS APPLY doesn't contain any row of left side table expression for which no result is obtained from right side table expression. CROSS APPLY work as a row by row INNER JOIN

Answer 2

Assuming sql-server is greater or equal than 2016 version. Then, string_split() function can be used:

select spl.u_id, s.name us_uid, s.age col_1, s.dob col_2, s.education col_3, s.department col_4 from @source s
    cross apply( select value u_id from string_split( (select u_id from @source s2 where s2.u_id = s.u_id ),',')
                where CHARINDEX(value,s.u_id,1) > 0) spl