Delimited Row of Data Using Cursor & Dynamic SQL

Question

Delimited Row of Data Using Cursor & Dynamic SQL

fggoulart

Old Hand

Points: 312
More actions
November 6, 2003 at 6:04 pm

#106473

Description:This stored procedure uses a single column in any table and creates
a delimited row of data in a table called tblTransformedList.
In order for this procedure to function correctly, the table
must contain a column of sequential data, such as an identity
column with the lowest value of 1.
Inputs:Table name, Column name, Name of identity column, and any
column delimiter.
Outputs:A delimited row of data.
To test this Stored Procedure you can run this code:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblColumnToTransform]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblColumnToTransform]
CREATE TABLE [dbo].[tblColumnToTransform] (
[ident] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (32)
) ON [PRIMARY]
INSERT INTO tblColumnToTransform (Name) VALUES ('Biff')
INSERT INTO tblColumnToTransform (Name) VALUES ('Rose')
INSERT INTO tblColumnToTransform (Name) VALUES ('Don')
INSERT INTO tblColumnToTransform (Name) VALUES ('Lance')
INSERT INTO tblColumnToTransform (Name) VALUES ('Manion')
After you create the Stored Procedure, call it like so:
exec procTransformColumnToDelimList 'tblColumnToTransform', 'name', 'ident', ','
Here is the Stored procedure. Comments are included in the body of the procedure.
Create Proc procTransformColumnToDelimList (
@table varchar(25),
@col varchar(25),
@colIdentity Varchar(25),
@Delimiter char(1)
)
as
/*
Description:This stored procedure uses a single column in any table and creates
a delimited row of data in a table called tblTransformedList.
In order for this procedure to function correctly, the table
must contain a column of sequential data, such as an identity
column with the lowest value of 1.
Inputs:Table name, Column name, Name of identity column, and any
column delimiter.
Outputs:A delimited row of data.
Date Created:6 Nov. 2003
Date Modified:
*/
--Create the table to hold the delimited row of data
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTransformedList]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblTransformedList]
CREATE TABLE [dbo].[tblTransformedList] (
[List] [varchar] (8000) --COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Declare @Query varchar(8000)
--Update the name column in the tblColumnToTransform table
--(or a column in a table of your choice) with the delimiter of your choice.
SET @Query = '
Update ' + @table + '
Set' + @col + ' = ' + @col + ' + ' + '''' + @Delimiter + ' ' + ''''
PRINT @Query
print ''
EXEC (@Query)
--Insert the first record from tblColumnToTransform into tblTransformedList
-- (the table to hold the delimited row of data)
SET @Query = '
INSERT INTO tblTransformedList (List)
SELECT ' + @col + '
FROM ' + @table + ' where ' + @colIdentity + ' = 1'
PRINT @Query
print ''
EXEC (@Query)
--Decalare the cursor to be used to loop through tblColumnToTransform
Declare @Cursor varchar(150)
Set @Cursor = 'declare MyCursor Cursor For Select ' + @col + ' , ' + @colIdentity + ' from ' + @table
Print @Cursor
exec (@Cursor)
open MyCursor
Close MyCursor
Open MyCursor
Fetch MyCursor into @col, @colIdentity
--while @@Fetch_Status is successful, loop
While @@Fetch_Status = 0
Begin
--Add data to the end of the List column in tblTransformedList
Declare @sql varchar(1000)
Set @sql = 'Update tblTransformedList Set List = List + ' +''''+ @col + ' ' +'''' + ' from ' + @table + ' Where ' + @colIdentity + ' > 1'
Print @sql
Exec (@SQL)
Fetch Next from MyCursor into @col, @colIdentity
End
--Close and deallocate cursor
close MyCursor
deallocate MyCursor
Declare @LenOfList as int
Select @LenOfList = Len(List)
From tblTransformedList
print @LenOfList
--Remove the last occurance of the delimiter in the list.
--You might want to remove this line if this isn't what you need.
Update tblTransformedList
Set List = Substring(List, 1, @LenOfList - 1)

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

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

How about just something like this?



CREATE PROC DelimitedList

(@Table sysname, @Col sysname, @Del char, @Ord sysname = NULL) AS

SET NOCOUNT ON

SET CONCAT_NULL_YIELDS_NULL ON

DECLARE @sql nvarchar(500), @List varchar(8000)

SELECT @sql = N'SELECT @List = ISNULL(@List + ''' + @Del + ''', '''') + ' + @Col + '

FROM ' + @Table + ISNULL('

ORDER BY ' + @Ord,'')

EXEC sp_executesql @sql, N'@List varchar(8000) OUTPUT', @List = @List OUTPUT

SELECT @List

Then, instead of having the result set table name hard-coded in the proc, use it like this:



CREATE TABLE #Temp(List varchar(8000))

INSERT #Temp

EXEC Delimitedlist 'tblColumnToTransform','Name',',','Ident'

Even easier if you don't need to support earlier versions of SQL Server and can just use a UDF.

--Jonathan

fggoulart Old Hand Points: 312 More actions · Answer 2

That works too and is a lot less code. It is not as intuitive to me so I’ll have to spend some time with it before I really get it.

MikeMcDonald Say Hey Kid Points: 663 More actions · Answer 3

For another possibility, see my code in "xp_sendmail problem". Uses a text column to generate the required string. Has the advantage that the string can grow longer than the 8K limit of a varchar field.