-2

I have data as below

name    subject
a       m1
b       m2
c       m1, m3
d       m4, m5, m6
e       m7

I would like to duplicate entire row where column subject contains , . The original data has many more columns than shown here. I will duplicate a row equal to number of , in the subject column, so I will repeat c 1 times and d 2 times

I don't know how many cells in the subject column would have , and there is no upper limit to number of values in a cell of the subject column

Then I would like to keep only one value on the duplicated rows such that all values from the subject column appear once

name    subject
a       m1
b       m2
c       m1
c       m3
d       m4
d       m5
d       m6
e       m7
Dale K
  • 25,246
  • 15
  • 42
  • 71
user2543622
  • 5,760
  • 25
  • 91
  • 159

1 Answers1

2

If you are running SQL Server 2016 or higher, you can use string_split():

select t.name, value as subject
from mytable t
cross apply string_split(subject, ', ')

string_split() returns a unique column, called value.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • could you explain how `cross apply` works? How would it ensure that we are correctly repeating rest of the columns? – user2543622 Sep 11 '20 at 23:31
  • @user2543622: that's [a lateral join](https://learn.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-ver15#using-apply). If you want more columns from the original table, just list them in the `select` clause. – GMB Sep 11 '20 at 23:38
  • can i say `select t.*, values as subject1` and then remove `subject`? – user2543622 Sep 11 '20 at 23:54
  • @user2543622: in SQL Server you cannot "remove" a column from the resultset. You can do `select t.*, value as subject1`, and you get both `suject` and `subject1` in the resultset. Else, you need to list all column names (expect `subject`) in the `select` clause. – GMB Sep 11 '20 at 23:57