sql-server - 如何在枢轴中找到平均值

Question

我在 sql server 2008 中有两个表

   TABLE [tblTagDescription](
   [ID] [bigint] IDENTITY(1,1) NOT NULL,
   [TopicID] [int] NULL,
   [TagID] [int] NULL,
   [FullTag] [nvarchar](50) NULL,
   [ValDecimal] [nvarchar](50) NULL,
   [ValBinary] [nvarchar](50) NULL,
   [GroupName] [nvarchar](50) NULL,
   [ReportTag] [nvarchar](50) NULL
)

And

   TABLE [tblDataLog](
   [ID] [bigint] IDENTITY(1,1) NOT NULL,
   [TagDescID] [bigint] NULL,
   [Value] [decimal](18, 2) NULL,
   [Date] [datetime] NULL,
)

在这里，tblDescription 的多个“Id”和 tblDataLog 的“TagDescID”可能有相同的组。这里“Group1”有 10 个 ID，从 1 到 10。在 tbldatalog 中这些 ID（从 1 到 10）可能有多个记录。我希望这些 ID 从 1 到作为列。我想要这些 ID 的平均值（从 1 到 10）。为此，我使用了枢轴：

 Declare @COls      nvarchar(max)
    Declare @SQL        nvarchar(max)
    DECLARE @COlsID NVARCHAR(MAX) = ''
    DECLARE @COlsAlias NVARCHAR(MAX) = ''

    IF OBJECT_ID('tempdb..##MYTABLE') IS NOT NULL DROP TABLE ##MYTABLE
    IF OBJECT_ID('tempdb..#tt') IS NOT NULL DROP TABLE #tt

    IF(@Group='A')
    BEGIN
        Select @COls=COALESCE(@Cols + '],[','') + z.ReportTag From 
        (Select Distinct T.ID, ReportTag From tblTagDescription T
        Where isnull(ReportTag,'')<>'' AND T.GroupName=@Group Group BY T.ID,T.ReportTag --order by T.ID 
        )z  
    END
    ELSE
    BEGIN
        SELECT 
           @COlsID = @ColsID + ',' + z.TagDescID, 
           @COlsAlias = @COlsAlias + ',' + z.TagDescID + ' AS ' + z.ReportTag   
        FROM
            (select TagDescID,ReportTag from(SELECT DISTINCT TOP 50 QUOTENAME(CONVERT(NVARCHAR(25), 
            tblDataLog.TagDescID )) TagDescID,TagdescID TID, 
            QUOTENAME(tblTagDescription.ReportTag) ReportTag 
             FROM tblDataLog 
                INNER JOIN tblTagDescription ON tblDataLog.TagDescID = tblTagDescription.ID 
                where tblTagDescription.GroupName=@Group 
             ORDER BY  tblDataLog.TagDescID )s 
             ) z
    END

    SET @COlsID= STUFF(@COlsID,1,1,'')
    SET @COlsAlias= STUFF(@COlsAlias,1,1,'')

    SET @SQL='select [DATE],SHIFT, ' + @COlsAlias + ' into ##MYTABLE from ( select [Date], AVG(Value), TagDescID,
                 (CASE  
                            WHEN ((DATEPART(hour,[DATE]))>6 and (DATEPART(hour,[DATE]))<14) THEN ''A'' 
                            WHEN ((DATEPART(hour,[DATE]))>=14 and (DATEPART(hour,[DATE]))<22) THEN ''B'' 
                            WHEN ((DATEPART(hour,[DATE]))>=22 or (DATEPART(hour,[DATE]))<6) THEN ''C'' 
                        END )AS SHIFT 

                 from tblDataLog Group by [Date],[TagDescID] )d pivot(max(Value) for TagDescID in (' + @COlsID + ')) piv;'

        EXEC (@SQL)

        select * from ##MYTABLE 

但是上面的查询给出了错误：

Msg 8155, Level 16, State 2, Line 8
No column name was specified for column 2 of 'd'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'Value'.
Msg 208, Level 16, State 0, Procedure Select_DataViewer, Line 58
Invalid object name '##MYTABLE'.

如何解决这个问题？

标签： sql-servertsqlpivot