Post reply

how to convert csv values to a table?

  • June 25, 2009 at 1:19 am

    #135739

    Hi,

    I couldnt find a way where i can get csv values in rows without using a while loop.

    how can i get the below ouput efficiently and fast?

    [Code]

    declare @S varchar(500)

    set @S='1,2,3,4,5'

    [/Code]

    Expected Output:

    s

    --

    1

    2

    3

    4

    5

  • June 25, 2009 at 2:13 am

    #1015122

    descentflower (6/25/2009)

    csv values in rows without using a while loop

    Hi,

    try this

    --create table #temp (name1 varchar(5))

    declare @S varchar(500)

    set @S='1,2,3,4,5'

    select @S = 'select ''' + replace (@s,',',''' union select ''')+''''

    insert into #temp (name1)

    exec (@s)

    select * from #temp

    ARUN SAS

  • June 25, 2009 at 2:41 am

    #1015137

    Thats amazing thank you so much !! :w00t:

    Anything to take care using

  • June 25, 2009 at 3:10 am

    #1015149

    arun.sas (6/25/2009)

    descentflower (6/25/2009)

    csv values in rows without using a while loop

    Hi,

    try this

    --create table #temp (name1 varchar(5))

    declare @S varchar(500)

    set @S='1,2,3,4,5'

    select @S = 'select ''' + replace (@s,',',''' union select ''')+''''

    insert into #temp (name1)

    exec (@s)

    select * from #temp

    ARUN SAS

    And how to do opposite of this in an efficient manner?

    like Column to a CSV.

  • June 25, 2009 at 3:11 am

    #1015150

    [font="Courier New"]DECLARE @S VARCHAR(500)

    DECLARE @X XML

    SELECT @S='1,2,3,4,5'

    SELECT @X = '<Data>' + REPLACE(@S,',','</Data><Data>') + '</Data>'

    SELECT N.value('.','varchar(max)') 'Result' from @X.nodes('Data') as T(N)[/font]

  • June 25, 2009 at 3:19 am

    #1015153

    declare @S varchar(500), @x xml;

    select @S='1,2,3,4,5',

    @x = '' + REPLACE(@s, ',' , '') + '';

    select a.value('.', 'int') as s

    from @x.nodes('/a') t(a)

    edit: I see 'Pandian S' beat me to it with the XML hack/solution :rolleyes:

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • June 25, 2009 at 3:34 am

    #1015161

    Pandian S (6/25/2009)

    [font="Courier New"]DECLARE @S VARCHAR(500)

    DECLARE @X XML

    SELECT @S='1,2,3,4,5'

    SELECT @X = '<Data>' + REPLACE(@S,',','</Data><Data>') + '</Data>'

    SELECT N.value('.','varchar(max)') 'Result' from @X.nodes('Data') as T(N)[/font]

    Hi,This one takes a string as an input.

    If i have 5 rows in a table like

    tbItem

    item

    -----

    1

    2

    3

    4

    5

    then how can i get 1,2,3,4,5 as my output.

  • June 25, 2009 at 4:26 am

    #1015184

    [font="Verdana"]DECLARE @TABLE1 TABLE (ID INT)

    INSERT @TABLE1 VALUES(1)

    INSERT @TABLE1 VALUES(2)

    INSERT @TABLE1 VALUES(3)

    INSERT @TABLE1 VALUES(4)

    INSERT @TABLE1 VALUES(5)

    DECLARE @IDs VARCHAR(MAX)

    SELECT @IDs = COALESCE(@IDs,'') + CAST(ID AS VARCHAR) + ',' FROM @TABLE1

    SELECT LEFT(@IDs,LEN(@IDs)-1) 'Result'

    (OR)

    ;WITH CTEs(Result) AS (SELECT CAST(ID AS VARCHAR) + ',' FROM @TABLE1 FOR XML PATH(''))

    SELECT LEFT(Result,LEN(Result)-1) 'Result' FROM CTEs[/font]

  • June 25, 2009 at 5:09 am

    #1015207

    Thanks a lot friends !! it helped me a lot !

Viewing 9 posts - 1 through 8 (of 8 total)

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