SQL使用单个表将行数据转换为列 [英] SQL converting rows data in to column using single table

问题描述

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

I am trying to convert one of DB table row in to column and using PIVOT function with cursors, here is the 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是我使用此视图创建的视图：

where AllCounterIds is a View that I created using this:

GO
CREATE VIEW AllCounterIds AS 
SELECT DISTINCT CounterId FROM AllCounters
GO

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

So problem is I have around 27993600 rows in table now by far, and when I execute the SQL it takes arount 4min and some 15 sec to give me the output and as per performance requirement its bad ... so my question is that, is there anyway where I can achieve my desired result but getting better performance?

只是为了让您知道表上也定义了簇索引...

And just to let you know cluster indexes are also defined on the table ...

推荐答案

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

I'm not sure it's actually faster for your case, but perhaps try something like this

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来获取整数列表，这样可以节省一些内存，从而节省时间。

The use of varchar instead of nvarchar is deliberate. It's not likely that you need unicode for a list of integers and it will save some memory, thus possibly time.

这篇关于SQL使用单个表将行数据转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！