SSRS Expression Split string in rows and column

Question

I am working with SQL Server 2008 Report service. I have to try to split string values in different columns in same row in expression but I can't get the excepted output. I have provided input and output details. I have to split values by space (" ") and ("-").

Input :

Sample 1:

ASY-LOS,SLD,ME,A1,A5,J4A,J4B,J4O,J4P,J4S,J4T,J7,J10,J2A,J2,S2,S3,S3T,S3S,E2,E2F,E6,T6,8,SB1,E1S,OTH AS2-J4A,J4B,J4O,J4P,J4S,J4T,J7,J1O,J2A,S2,S3,J2,T6,T8,E2,E4,E6,SLD,SB1,OTH

Sample 2:

A1 A2 A3 A5 D2 D3 D6 E2 E4 E5 E6 EOW LH LL LOS OTH P8 PH PL PZ-1,2,T1,T2,T3 R2-C,E,A RH RL S1 S2-D S3

Output should be:

Thank you.

Answer 1

I wrote this before I saw your comment about having to do it in the report. If you can explain why you cannot do this in the dataset query then there may be a way around that.

Anyway, here's one way of doing this using SQL

DECLARE @t table (RowN int identity (1,1), sample varchar(500))
INSERT INTO @t (sample) SELECT 'ASY-LOS,SLD,ME,A1,A5,J4A,J4B,J4O,J4P,J4S,J4T,J7,J10,J2A,J2,S2,S3,S3T,S3S,E2,E2F,E6,T6,8,SB1,E1S,OTH AS2-J4A,J4B,J4O,J4P,J4S,J4T,J7,J1O,J2A,S2,S3,J2,T6,T8,E2,E4,E6,SLD,SB1,OTH'
INSERT INTO @t (sample) SELECT 'A1 A2 A3 A5 D2 D3 D6 E2 E4 E5 E6 EOW LH LL LOS OTH P8 PH PL PZ-1,2,T1,T2,T3 R2-C,E,A RH RL S1 S2-D S3'

drop table if exists #s1
SELECT RowN, sample, SampleIdx = idx, SampleValue = [Value]
into #s1
from @t t
 CROSS APPLY 
 spring..fn_Split(sample, ' ') as x 
 
drop table if exists #s2
SELECT 
    s1.*
    , s2idx = Idx
    , s2Value = [Value]
    into #s2
    FROM #s1 s1
    CROSS APPLY spring..fn_Split(SampleValue, '-')

SELECT SampleKey = [1],
       Output = [2]  FROM #s2
PIVOT (
        MAX(s2Value)
        FOR s2Idx IN ([1],[2])
) p

This produced the following results

---

If you do not have a split function, here is the script to create the one I use

---

CREATE FUNCTION [dbo].[fn_Split]

/* Define I/O parameters WARNING! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE! */
    (@pString VARCHAR(8000)
    ,@pDelimiter CHAR(1)

    )
RETURNS TABLE
    WITH SCHEMABINDING
AS

RETURN

/*"Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000: enough to cover VARCHAR(8000)*/
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
        )--10E+1 or 10 rows
    ,E2(N) AS (SELECT 1 FROM E1 a,E1 b)--10E+2 or 100 rows
    ,E4(N) AS (SELECT 1 FROM E2 a,E2 b)--10E+4 or 10,000 rows max
    
    /* This provides the "base" CTE and limits the number of rows right up front
    for both a performance gain and prevention of accidental "overruns" */
    ,cteTally(N) AS (
        SELECT TOP (ISNULL(DATALENGTH(@pString), 0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM E4
        )
    
    /* This returns N+1 (starting position of each "element" just once for each delimiter) */
    ,cteStart(N1) AS (
        SELECT 1 UNION ALL
        SELECT t.N + 1 FROM cteTally t WHERE SUBSTRING(@pString, t.N, 1) = @pDelimiter
        )
    
    /* Return start and length (for use in SUBSTRING later) */
    ,cteLen(N1, L1) AS (
        SELECT s.N1
            ,ISNULL(NULLIF(CHARINDEX(@pDelimiter, @pString, s.N1), 0) - s.N1, 8000)
        FROM cteStart s
        )

/* Do the actual split.
The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. */
SELECT
     idx = ROW_NUMBER() OVER (ORDER BY l.N1)
    ,value = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l