SSRS Question - How to break the parameters with multiple values to a dataset or #table

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  June 16, 2017 at 11:34 am

  #343274

  Sorry 
  I answered my own question here .....  Found the following code in a website. Does the job well.

  Declare @PlanCodes varchar(500);
Select @PlanCodes = 'ABC,BXX,FFF,DGG,HRRRRT';

  ;
WITH cte_plancodes_pieces
  AS (SELECT 1         AS ID,
      1         AS StartString,
      Charindex(',', @PlanCodes) AS StopString
   UNION ALL
   SELECT id + 1,
      stopstring + 1,
      Charindex(',', @PlanCodes, stopstring + 1)
   FROM cte_plancodes_pieces
   WHERE stopstring > 0),
  cte_plancodes_split
  AS (SELECT CONVERT(NVARCHAR, Substring(@PlanCodes, startstring, CASE
                        WHEN
             stopstring > 0 THEN
             stopstring - startstring
             ELSE Len(@PlanCodes)
                       END)) AS
      PlanCode
   FROM cte_plancodes_pieces
)
Select * FROM cte_plancodes_split

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  June 16, 2017 at 11:35 am

  #1947142

  mw112009 - Friday, June 16, 2017 11:34 AM

  Sorry 
  I answered my own question here .....  Found the following code in a website. Does the job well.

  Declare @PlanCodes varchar(500);
Select @PlanCodes = 'ABC,BXX,FFF,DGG,HRRRRT';

  ;
WITH cte_plancodes_pieces
  AS (SELECT 1         AS ID,
      1         AS StartString,
      Charindex(',', @PlanCodes) AS StopString
   UNION ALL
   SELECT id + 1,
      stopstring + 1,
      Charindex(',', @PlanCodes, stopstring + 1)
   FROM cte_plancodes_pieces
   WHERE stopstring > 0),
  cte_plancodes_split
  AS (SELECT CONVERT(NVARCHAR, Substring(@PlanCodes, startstring, CASE
                        WHEN
             stopstring > 0 THEN
             stopstring - startstring
             ELSE Len(@PlanCodes)
                       END)) AS
      PlanCode
   FROM cte_plancodes_pieces
)
Select * FROM cte_plancodes_split

  So I get the output as 

  ABC
  BXX
  FFF
  DGG
  HRRRRT

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  June 20, 2017 at 9:26 am

  #1947396

  Nice to see you solving a problem on your own.   You should also take a closer look at a highly performant string splitting function that Jeff Moden wrote back in 2008.   It's named DelimitedSplit8K, and you can read all about how it works in excruciating detail here:

  http://www.sqlservercentral.com/articles/72993/

  There's a LOT to learn there, and it's quite worthwhile to go through it.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)