Insert and update in a cursor in SQL Server

Question

I have a Table shown below

DateRange
10/1
11/1
12/1

I am looping through this table using cursor and trying to populate a temp table

Create table #BidStatusCalculation1
(    
  Code nvarchar(max), 
  Data int  
)
Declare @monthStart varchar(20)   
DECLARE cur CURSOR FOR SELECT DateRange FROM @dateRange
OPEN cur
FETCH NEXT FROM cur INTO @monthStart
WHILE @@FETCH_STATUS = 0 BEGIN

Insert into #BidStatusCalculation1
    select 'SL Payroll',Count(*) from DashboardData
    where DataType = 'Bid'
    and CONVERT(NVARCHAR, CreatedDate, 105) = convert(datetime, 
                                             (@monthStart+'/'+@RequestYear))  

FETCH NEXT FROM cur INTO @monthStart
END
CLOSE cur    
DEALLOCATE cur

This gives me a result like below

Code          Data
SL Payroll    22
SL Payroll    33
SL Payroll    43

I am looking to modify the table So I get just one row like

Code         Data1   Data2   Data3
SL Payroll   22       33      43

Can someone please tell me what a good approach might be to first Insert Data inside a table on the first loop and then update it. I can put a variable to count the loop iteration and then write separate insert and update but was wondering if there is a better way.

Thanks

You would benefit from understanding the `PIVOT` function: Microsoft: https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017 StackOverflow: https://stackoverflow.com/questions/10428993/understanding-pivot-function-in-t-sql — gbeaven, Jan 10 '19 at 18:04

Ilyes · Accepted Answer · 2019-01-10T18:52:52.303

2

Use PIVOT as

SELECT Code,
       [22] Data1,
       [33] Data2,
       [43] Data3
FROM
    (
      SELECT *
      FROM T
    ) TBL
    PIVOT
    (
      MAX(Data) FOR Data IN([22],[33],[43])
    ) PVT

Demo

For dynamic pivot

CREATE TABLE T
(
  Code VARCHAR(45),
  Data INT
);

INSERT INTO T VALUES
('SL Payroll',    22),
('SL Payroll',    33),
('SL Payroll',    43);

DECLARE @IN VARCHAR(MAX) = '',
        @Cols VARCHAR(MAX) = '';

SELECT @IN = @IN + ',' + QUOTENAME(Data),
       @Cols = @Cols + ',' + QUOTENAME(Data) + ' AS Data'+
               CAST(ROW_NUMBER()OVER(ORDER BY (SELECT NULL)) AS VARCHAR)
FROM T;

SELECT @IN = STUFF(@IN, 1, 1, ''), @Cols = STUFF(@Cols, 1, 1, '');

DECLARE @SQL NVARCHAR(MAX) = N'SELECT Code, ' +
                               @Cols+
                               ' FROM (SELECT * FROM T) TBL PIVOT (MAX(Data) FOR Data IN('+
                               @IN+
                               ')) PVT';
     EXECUTE sp_executesql @SQL;

Demo

edited Jan 10 '19 at 18:52

answered Jan 10 '19 at 18:34

Ilyes

14,640
4
29
55

Thanks..I am getting NULL..the 22,33 etc values were only for demo purpose..The values are dynamically generated. – SP1 Jan 10 '19 at 18:37
@user1221989 You need to write a Dynamic Pivot – Ilyes Jan 10 '19 at 18:38
Ok Thanks..I ll do some research on how Pivot works. – SP1 Jan 10 '19 at 18:43
@user1221989 Check the updates. But remember try to learn what the code do, not just copy/paste it and don't care to learn. – Ilyes Jan 10 '19 at 18:52

Insert and update in a cursor in SQL Server

1 Answers1

Linked

Related