SQL使用单个表将行数据转换为列

admin

SQL使用单个表将行数据转换为列

sql

我正在尝试将数据库表行中的一个转换为列，并使用带有游标的PIVOT函数，这是Sql：

DECLARE Cur CURSOR FOR
  SELECT DISTINCT CounterId
  FROM AllCounterIds

DECLARE @Temp NVARCHAR(MAX),
  @AllCounterIds NVARCHAR(MAX),
  @CounterIdQuery NVARCHAR(MAX)

SET @AllCounterIds = ''

OPEN Cur
-- Getting all the movies
FETCH NEXT FROM Cur INTO @Temp
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @AllCounterIds = @AllCounterIds + '[' + @Temp + '],'
 FETCH NEXT FROM Cur INTO @Temp
END

CLOSE Cur
DEALLOCATE Cur

SET @AllCounterIds = SUBSTRING(@AllCounterIds, 0, LEN(@AllCounterIds))

SET @CounterIdQuery = 'SELECT DateTime, ' + @AllCounterIds + '
FROM 
(SELECT Type, DateTime, Value, AllCounterIds.CounterId
FROM AllCounterIds
 INNER JOIN AllCounters
  ON AllCounterIds.CounterId = AllCounters.CounterId) S
PIVOT
(
SUM (Value)
FOR CounterId IN
(' + @AllCounterIds + ')) AS pvt'

EXEC sp_executesql @CounterIdQuery

其中AllCounterIds是我使用以下方法创建的视图：

GO
CREATE VIEW AllCounterIds AS 
SELECT DISTINCT CounterId FROM AllCounters
GO

所以问题是，到目前为止，我现在在表中大约有27993600行，而当我执行SQL时，它需要4分钟的时间和大约15秒的时间才能提供给我输出，并且根据性能要求，它的性能很差…所以我的问题是无论如何，我可以达到我想要的结果但获得更好的性能？

只是为了让您知道表上也定义了群集索引…

阅读 207

2021-06-07

共1个答案

admin

我不确定您的情况实际上会更快，但也许尝试这样的方法

declare
    @allcounterids varchar(max),
    @counteridquery varchar(max)
;

select
    @allcounterids = stuff((
    select 
            '],[' + cast([CounterId] as varchar(32))
        from
            allcounterids
        for xml path('')), 1, 2, '') + ']'
;

set @counteridquery = 'select
        s.datetime, pvt.*
    from
        (SELECT Type, DateTime, Value, AllCounterIds.CounterId
            FROM AllCounterIds
            INNER JOIN AllCounters
                ON AllCounterIds.CounterId = AllCounters.CounterId
        ) S
        PIVOT
        (
            SUM (Value)
            FOR CounterId IN
            (' + @AllCounterIds + ')
        ) AS pvt;'
;
execute(@counteridquery);

故意使用varchar代替代替nvarchar。您不太可能需要 unicode来获取整数列表，这样可以节省一些内存，从而节省时间。

2021-06-07