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.

Does this have to be done in the report or are you able to change the dataset and do thew work there instead? Up might get more answers if you can do it in the dataset query — Alan Schofield, Aug 13 '20 at 09:11
this process have to do in expression or report Property->Code in report. — nanda9894, Aug 13 '20 at 09:17

score 1 · Accepted Answer · answered Aug 13 '20 at 11:16

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

SSRS Expression Split string in rows and column

1 Answers1