-1

I want to convert a comma separated list back into a table.

For eg.

I have a table which looks like this

Sid  roleid
  500    1,5,
  501    1,5,6,

I want output like this

Sid roleid
500   1
500   5
501   1
501   5
501   6

Please help.

Create table #temp(Sid int,roleid varchar(100))
Insert into #temp values(500,'1,5,'),(501,'1,5,6,')
Dale K
  • 25,246
  • 15
  • 42
  • 71
Atk
  • 754
  • 1
  • 4
  • 12
  • Tag the DBMS version that you are using. – Yogesh Sharma Apr 30 '20 at 09:56
  • 1
    Does this answer your question? [How to convert comma separated NVARCHAR to table records in SQL Server 2005?](https://stackoverflow.com/questions/15585632/how-to-convert-comma-separated-nvarchar-to-table-records-in-sql-server-2005) – Dale K Apr 30 '20 at 10:07

3 Answers3

2

Using STRING_SPLIT() means, that you are working on SQL Server 2016 (oder higher).

However, STRING_SPLIT() has a huge draw back: It is not guaranteed to return the items in the expected order (see the docs, section "Remarks"). In my eyes this is an absolut show stopper...

But - luckily - there is a fast and easy-to-use workaround in v2016+:

Create table #temp(Sid int,roleid varchar(100))
Insert into #temp values(500,'1,5,'),(501,'1,5,6,');

SELECT t.[Sid]
      ,A.[key] AS position
      ,A.[value] AS roleid
FROM #temp t
CROSS APPLY OPENJSON(CONCAT('["',REPLACE(t.roleid,',','","'),'"]')) A
WHERE A.[value]<>'';

A simple number array 1,3,5 needs nothing more than brackets to be a JSON array ([1,3,5]). In your case, due to the trailing comma, I deal with it as strings. 1,3,5, will be taken as array of strings: ["1","3","5",""]. The final empty string is taken away by the WHERE clause. The rest is easy...

Other than STRING_SPLIT() the docs proof, that OPENJSON will reflect an item's position in the [key] column:

When OPENJSON parses a JSON array, the function returns the indexes of the elements in the JSON text as keys.

General hint: avoid STRING_SPLIT() as lons as there is no additional key/position column added to its result set.

Shnugo
  • 66,100
  • 9
  • 53
  • 114
1

Use sring_split():

select t.sid, value rid
from t
cross apply string_split(t.roleid, ',') rid
order by t.sid, rid
GMB
  • 216,147
  • 25
  • 84
  • 135
  • I try to avoid `STRING_SPLIT()` as long as it does not guarantee the sort order... (see my answer) – Shnugo May 04 '20 at 08:47
  • @Shnugo: in this very use case the ordering does not matter, so `string_split()` is good enough. But in general I do agree with you. `OPENJSON` is a nice alternative, upvoted your answer. – GMB May 04 '20 at 12:15
1

Use string_split() :

select t.sid, spt.value
from table t cross apply
     string_split(t.roleid, ',') as spt
order by t.sid, spt.value;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
  • I try to avoid `STRING_SPLIT()` as long as it does not guarantee the sort order... (see my answer) – Shnugo May 04 '20 at 08:47