cte in sql server with temp table and split string

Question

I have table with two colums ID and rights. Rights column will keep 250 fixed characters in 0,1 form like '010111100000000....250 times'. Now I have to split the rights column string and get the results in temp table which will have the structure ID, Rights(0 or 1), Position(1 to 250). Suppose I have 5 rows initially then in temp table I will get 5*250 = 1250 rows.

I have split the single string and used cursor but now I want to avoid cursor. How I can achieve this.

declare  @temp table(Chars int not null, RowID int not null)

    --Split the rights string into 250 char rows with RowID as each position
    ;with cte as
    (
        select substring(@rights, 1, 1) as Chars,
                stuff(@rights, 1, 1, '') as rights,
                1 as RowID
        union all
        select substring(rights, 1, 1) as Chars,
                stuff(rights, 1, 1, '') as rights,
                RowID + 1 as RowID
        from cte
        where len(rights) > 0

    )

    --Get the values in a temporary table except 0
    insert into @temp select Chars, RowID from cte  option (MAXRECURSION 300);

Gottfried Lesigang · Accepted Answer · 2017-03-02 08:32:36Z

1

What about this?

The idea is: Get a list of 250 running numbers and use SUBSTRING to read one character from each position:

DECLARE @tbl TABLE (ID INT IDENTITY,Rights VARCHAR(250));
INSERT INTO @tbl VALUES
 ('1011101110000101010111000...'), ('0100010111100010101...');

WITH Nr250 AS
(SELECT TOP 250 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Nr FROM master.dbo.spt_values)

SELECT t.ID 
      ,t.Rights
      ,Nr AS Position
      ,SUBSTRING(t.Rights,Nr,1) AS PosDigit
--INTO #SomeTempTable
FROM Nr250
CROSS JOIN @tbl AS t

If you want to write this into a temp table, just remove the -- before INTO

answered Mar 2, 2017 at 8:32

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

cte in sql server with temp table and split string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related