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 youThis might help with part of it:

SELECT '"' + Row1 + '","' + Col1 + '","' + Col2 + '","' + Col3 + + '";'|||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.|||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.|||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|||Funny you should ask

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

bottom row

How to display bottom row should be in dotted line

One way of doing this is to put a group in with a grouping of =1 (will force only one group) and then in the group footer make the top dotted. If you need this on every page, just have the group footer displayed on every page.|||

Thanks Lonnie

Can you explain indetail.

Boosting up Fuzzy Lookups performance

Hello there,

Is there any way to increase the speed of search while performing fuzzy lookups against a 300,000 row Table ?

include a index for the column