Converting Rows to Column with Segmentation

First of all, my apologies for the long time between posts.  My job (that pays me money) has kept me fairly busy over the past few months and the few that do follow this blog have been asking when I am going to do my next post… Well here it is.

Recently I found myself with the need to convert rows in a table into a single column.   My limitation was that I was only able to pull in 200 rows values per column and those values had to be comma delimited.  Meaning if I had 3118 rows in my source table and I needed to convert those 3118 rows into a single column, but could only have a maximum 200 row values in my column, my output would be 16 rows (3118/200 = 15.59 rounded up to 16).  My first inclination was to pivot the data; but that only allows me to create columns in excess of the values I want to pass into those columns.

Here I decided to to create a simple loop that can be integrated into a stored procedure or user defined function that worked perfectly.

CREATE TABLE dbo.DISPOSED
(DISPOSEDVALUE VARCHAR(10), RAN date)
 
— This will handle values that have already been propagated in my target table
 
CREATE TABLE dbo.RowsToColumns
(ConvertedRows VARCHAR(MAX),
 Iteration  VARCHAR(3)
 )
 
— My target table
 
TRUNCATE TABLE dbo.DISPOSED
TRUNCATE TABLE dbo.RowsToColumns
 
DECLARE @incCounter INT = 1
While (SELECT COUNT(*) FROM dbo.SourceTable) > (SELECT COUNT(*) FROM dbo.DISPOSED)  — A check against the number of rows in source vs. the number of rows remaining
BEGIN
 
INSERT INTO STG.DISPOSED
SELECT TOP 200 A.FIELD1, NULL AS RAN
FROM dbo.SourceTable A
LEFT JOIN STG.DISPOSED B
on A.FIELD1 = B.DISPOSEDVALUE
WHERE B.DISPOSEDVALUE IS NULL
 
— Grabs the top 200 records from source that have not already been propagated in the target table
 
      DECLARE @Output VARCHAR(MAX) = ”
      SELECT @Output = COALESCE(@Output+’,’, ”) + CONVERT(varchar(10), DISPOSEDVALUE)
      FROM STG.DISPOSED
      WHERE RAN is NULL
 
— Input the data with a comma delimiter
 
INSERT INTO dbo.RowsToColumns
SELECT SUBSTRING(@Output,2,LEN(@Output)) as ConvertedRows, CONVERT(VARCHAR(3),@incCounter) as Iteration
 
— Insert data into the target data, and update the values that have just been propagated.
 
UPDATE      dbo.DISPOSED
SET RAN = GETDATE()
WHERE RAN IS NULL
 
— Increment a counter
 
set @incCounter = @incCounter+1
 
if (SELECT COUNT(*) FROM dbo.SourceTable) = (SELECT COUNT(*) FROM dbo.DISPOSED)
break
ELSE CONTINUE
end

Worked like a charm.  You’re probably reading this wondering “what in the heck can this be used for”… Well I’d like to tell you but that’s my secret.

I am going to try my best to get back to posting once a month.

Advertisements

Tags: ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: