Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2007
    Posts
    6

    Unanswered: Breaking a row of data into multiple CSV rows

    Does anyone have a routine that takes a row of data from database, duplicates/triplicates it, appends some information to it and writes it out as 2/3 CSV rows.

    Basically I need to do the following.

    Selected from database:
    Row1 Col1 Col2 Col3

    Output:
    Row1 Col1, Col2, Col3, abc
    Row1 Col1, Col2, Col3, def
    Row1 Col1, Col2, Col3, ghi

    Thank you

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This might help with part of it:
    Code:
    SELECT '"' + Row1 + '","' + Col1 + '","' + Col2 + '","' + Col3 + + '";'
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2006
    Location
    Momence, IL USA
    Posts
    56
    You could do a SELECT INTO 3 times for each "row" in the original table. It would be like:

    SELECT INTO newtable
    Col1, Col2, Col3, 'abc' AS Col4
    FROM oldtable
    WHERE Row=1

    SELECT INTO newtable
    Col1, Col2, Col3, 'def' AS Col4
    FROM oldtable
    WHERE Row=1

    SELECT INTO newtable
    Col1, Col2, Col3, 'ghi' AS Col4
    FROM oldtable
    WHERE Row=1

    Then you would convert newtable to CSV afterward (with BCP maybe).
    That's assuming there is a column named Row with a value of 1. But if you have a key column ( a field with unique values), the new table can't use that as a key column because you'll be making three copies of each row.
    But since your example wasn't very specific, either is the answer.

    Another method is to use a cursor and process each row.
    Don't Bogart That Squishee!

  4. #4
    Join Date
    Feb 2007
    Posts
    6
    I think using the cursor would be a better option since the number of rows that i need to "break" are in thousands.

    I am just not sure how to use cursor.

  5. #5
    Join Date
    Jul 2005
    Location
    New Zealand
    Posts
    61
    Have a look in BOL (Books online) to see how cursors, but be warned that cursors can be inefficient, particularly with a 'large' number of rows - some people on this forum positively detest them!
    Think about using temporary tables/table variables instead.

    Lempster

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Funny you should ask

    Code:
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_CSVTable]') and xtype in (N'FN', N'IF', N'TF'))
    drop function [dbo].[udf_CSVTable]
    GO
    
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS OFF 
    GO
    
    
    
    CREATE FUNCTION udf_CSVTable(@Str varchar(7000))
    RETURNS @t table (numberval int, stringval varchar(100), DateVal datetime)
    AS
    BEGIN
    
    DECLARE @i int, @c varchar(100)
    
    SELECT @Str = @Str + ',', @i = 1, @c = ''
    
    WHILE  @i <= len(@Str)
      BEGIN
    	IF substring(@Str,@i,1) = ','
    	  BEGIN
    		INSERT INTO @t(numberval, stringval, DateVal)
    		     VALUES (	  CASE WHEN isnumeric(@c)=1 THEN @c else Null END
    				, rtrim(ltrim(@c))
    				, CASE WHEN isdate(@c)=1 then @c else Null END)
    		SET @c = ''
    	  END
    	ELSE
    		SET @c = @c + SUBSTRING(@Str,@i,1)
    	SET @i = @i +1
      END
    RETURN
    END
    
    
    
    
    
    
    
    
    GO
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •