Home > Uncategorized > Combine Multiple Rows to a Single Row in TSQL

Combine Multiple Rows to a Single Row in TSQL

DECLARE @CUSTNO [varchar](50)
DECLARE @PREV_CUSTNO [varchar](50)
DECLARE @PRICE [decimal](20, 2)
DECLARE @CNT bigint
set @PREV_CUSTNO = ‘DUMMY’
set @CNT = 0

DECLARE db_cursor CURSOR FOR
SELECT CUSTNO, PRICE FROM MyDatabase.myschema.MyColTable

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @CUSTNO , @PRICE

WHILE @@FETCH_STATUS = 0
BEGIN

IF @PREV_CUSTNO = @CUSTNO
set @CNT = @CNT + 1
ELSE
set @CNT = 1

set @PREV_CUSTNO = @CUSTNO

IF @CNT = 1
insert into [myschema].[MyRowTable] ([ColAccountNUM],[ColPRICE1],InsertDate) values (@CUSTNO,@PRICE,getdate())
ELSE
DECLARE @sql nvarchar(max) = ‘update [myschema].[MyRowTable] set ColPRICE’+ cast(@CNT as varchar)+’ = ‘ +cast(@PRICE as varchar)+ ‘
where [ColAccountNUM] = ‘ +@CUSTNO
exec (@sql)
print (@sql)

FETCH NEXT FROM db_cursor INTO @CUSTNO , @PRICE
END
CLOSE db_cursor
DEALLOCATE db_cursor

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

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: