Split comma separated values in a particular comma postion using SQL or SSRS report

Question

I have a field in SSRS that is concatenated values like

1234,1456,3456,7890,3457,3245,4345

I need to break/split after 8th comma or in a particular position in next row like:

1234,1456,3456,
7890,3457,3245,
4345

Here values are dynamic but, we have to split/break at every 8th or particular comma

How you are getting value like 1234,1456,3456,7890,3457,3245,4345 in first place ? May be answer lies there. — KumarHarsh, Jan 06 '20 at 07:36

score 2 · Answer 1 · answered Jan 05 '20 at 13:53

2

In your example text, all the values have four characters. If that is the case, a simple recursive CTE does what you want:

with cte as (
      select convert(varchar(max), NULL) as val, convert(varchar(max), field) as rest, 0 as lev
      from t
      union all
      select left(rest, 15) as val, stuff(rest, 1, 15, '') as rest, lev+1
      from cte
      where rest <> ''
     )
select val
from cte
where lev > 0;

Here is a db<>fiddle.

answered Jan 05 '20 at 13:53

Gordon Linoff

1,242,037
58
646
786

Dear sir, What happens if the length of each item is different with `4` ? Because according to OP said that `Here values are dynamic but, we have to split/break at 3rd comma` – Nguyễn Văn Phong Jan 05 '20 at 14:03
I was wondering whether we can use `CHARINDEX` to find the index exactly then `LEFT` to substring instead of hard code `15`? – Nguyễn Văn Phong Jan 05 '20 at 14:24
@Anonymous . . . This answer responds to the data you have in your question. – Gordon Linoff Jan 05 '20 at 18:08

score 2 · Answer 2 · answered Jan 05 '20 at 15:44

2

Once you've grabbed a copy of DelimitedSplit8K_LEAD (as STRING_SPLIT has no concept of ordinal positions) you can split the string and then "re-aggregate" it.

Using SQL Server 2017+:

DECLARE @YourString varchar(8000) = '1234,1456,3456,7890,3457,3245,4345';

WITH Split AS(
    SELECT DS.Item,
           DS.ItemNumber,
           (DS.ItemNumber - 1) / 3 AS Grp
    FROM dbo.DelimitedSplit8K_LEAD(@YourString,',') DS)
SELECT STRING_AGG(S.Item,',') WITHIN GROUP (ORDER BY S.ItemNumber ASC) AS NewString
FROM Split S
GROUP BY S.Grp;

SQL Server 2016-:

DECLARE @YourString varchar(8000) = '1234,1456,3456,7890,3457,3245,4345';

WITH Split AS(
    SELECT DS.Item,
           DS.ItemNumber,
           (DS.ItemNumber - 1) / 3 AS Grp
    FROM dbo.DelimitedSplit8K_LEAD(@YourString,',') DS)
SELECT STUFF((SELECT ',' + sq.Item
              FROM Split sq
              WHERE sq.Grp = S.Grp
              ORDER BY sq.ItemNumber
              FOR XML PATH(''),TYPE).value('.','varchar(8000)'),1,1,'') AS MewString

FROM Split S
GROUP BY S.Grp;

answered Jan 05 '20 at 15:44

Thom A

88,727
11
45
75

How to use this one in user defined table? – Kalleshi KS Jan 06 '20 at 09:14
What do you mean "in" a User Defined Table? A View? @KalleshiKS ? A `TABLE` cannot be defined by the a statement. – Thom A Jan 06 '20 at 09:16
Using table have to implement the above task, logic is correct but, need to use in table – Kalleshi KS Jan 06 '20 at 09:18
YOu're not making sense, @KalleshiKS . You can't use statements like that "in" a table. A computed column's definition can only be a scalar value, it can't return multiple results. This makes no sense to be a computed Column's value as it returns multiple rows. SQL Server has no "array" data type.. – Thom A Jan 06 '20 at 09:19
Yes, I understood @Larnu. Can we do in SSRS report level – Kalleshi KS Jan 07 '20 at 04:01

Zhorov · Answer 3 · 2020-01-06T09:19:02.347

If you use SQL Server 2016+, you may use an approach, based on JSON. Just transform the input text into a valid JSON array and parse this array with OPENJSON():

Example with text:

Statement:

DECLARE @json nvarchar(max) = N'1234,1456,3456,7890,3457,3245,4345'

SELECT CONCAT(
   MAX(CASE WHEN CONVERT(int, [key]) % 3 = 0 THEN [value] END),
   MAX(CASE WHEN CONVERT(int, [key]) % 3 = 1 THEN [value] END),
   MAX(CASE WHEN CONVERT(int, [key]) % 3 = 2 THEN [value] END)
   ) AS OutputText 
FROM OPENJSON(CONCAT(N'["', REPLACE(@json, N',', N',","'), N'"]'))
GROUP BY (CONVERT(int, [key]) / 3)

Result:

---------------
OutputText
---------------
1234,1456,3456,
7890,3457,3245,
4345

Example with table:

Table:

CREATE TABLE Data (TextData nvarchar(max))
INSERT INTO Data (TextData)
VALUES (N'1234,1456,3456,7890,3457,3245,4345')

Statement:

SELECT d.TextData, c.OutputData
FROM Data d   
CROSS APPLY (
   SELECT CONCAT(
      MAX(CASE WHEN CONVERT(int, [key]) % 3 = 0 THEN [value] END),
      MAX(CASE WHEN CONVERT(int, [key]) % 3 = 1 THEN [value] END),
      MAX(CASE WHEN CONVERT(int, [key]) % 3 = 2 THEN [value] END)
     ) AS OutputData 
   FROM OPENJSON(CONCAT(N'["', REPLACE(d.TextData, N',', N',","'), N'"]'))
   GROUP BY (CONVERT(int, [key]) / 3)
) c

Result:

---------------------------------------------------
TextData                            OutputData
---------------------------------------------------
1234,1456,3456,7890,3457,3245,4345  1234,1456,3456,
1234,1456,3456,7890,3457,3245,4345  7890,3457,3245,
1234,1456,3456,7890,3457,3245,4345  4345

How to use this one in user defined table for specific column — Kalleshi KS, Jan 06 '20 at 09:09
@KalleshiKS With an additional `APPLY` operator for example. See updated answer. — Zhorov, Jan 06 '20 at 09:19
Thanks for the answer @PythoMaster202. In SSRS report textbox values are keep o increasing the above values, can you help me to mplement the logic in SSRS report — Kalleshi KS, Jan 07 '20 at 04:18

Split comma separated values in a particular comma postion using SQL or SSRS report

3 Answers3