我有一个如下所示的表格
DateRange
10/1
11/1
12/1
我正在使用游标遍历此表并尝试填充临时表
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
我想修改表格 所以我只有一行
Code Data1 Data2 Data3
SL Payroll 22 33 43
有人可以告诉我在第一个循环中首先在表中插入数据然后更新它的好方法是什么。我可以放置一个变量来计算循环迭代,然后编写单独的插入和更新,但想知道是否有更好的方法。
谢谢
使用PIVOT
如
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;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句