Archive

Archive for December, 2015

Combine Multiple Rows to a Single Row in TSQL

December 11, 2015 Leave a comment

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

Categories: Uncategorized