在 SQL Server 中的游标中插入和更新 [英] Insert and update in a cursor in SQL Server
本文介绍了在 SQL Server 中的游标中插入和更新的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一张如下所示的表格
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
这给了我如下的结果
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.
谢谢
推荐答案
使用 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
对于动态枢轴
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;
这篇关于在 SQL Server 中的游标中插入和更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文