Post reply

change sequence of seperated numbers to row inside cte

  • October 10, 2010 at 9:55 pm

    #230223

    how can i change the received input of '2,3,4' to a row with column name id. here is sample data, and what i tried,but not able to convert the @input paramter to rows like i did using union.

    do i need to use loop through all values then insert that into a table or is there any other shorter way.

    declare @LocationTable TABLE(

    [ID] [int] NOT NULL,

    [LocationName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [ParentID] [int] NULL,

    [LevelID] [int] NULL

    )

    insert into @LocationTable (id,locationname,parentid,levelid)

    select1,'Afghanistan',NULL,1 union

    select2,'Kabul',1,2 union

    select3,'Kabul Province',2,3 union

    select4,'Dehsabz',2,3 union

    select5,'Nangarhar',1,2 union

    select6,'Jalalabad',5,3 union

    select7,'khogyani',5,3 union

    select8,'khogyani village',7,4

    /*

    -- this will be the input parameter

    declare @ids varchar(4000)

    set @ids='2,7' */

    ;with cte1

    as

    (

    select 2 as id union

    select 7 as id

    -- This part need to be change so if i pass 3 id it shall handle that rather then i had to use union statement.

    ),

    cte(Id,locationname,parentid,levelid)

    as

    (

    select id,locationname,parentid,levelid

    from @LocationTable where id in(select id from cte1)

    union all

    select a.id,a.locationname,a.parentid,a.levelid

    from @LocationTable a inner join cte on a.parentid=cte.id

    )

    select * from cte order by id

    Thanks for your help in advance.

  • October 10, 2010 at 10:16 pm

    #1234768

    Here is the latest version of the Delimited Split Function.

    You would use it like:

    declare @test-2 varchar(20);

    set @test-2 = '2,7,12';

    SELECT ID = convert(int, Item)

    FROM dbo.DelimitedSplit8K(@test, ',') ds;

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • October 11, 2010 at 2:57 am

    #1234831

    thanks alot wayne for your reply.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply