在SQL Server 2008中透视多个列

I am trying to convert column to rows. I am beginner to PIVOT. Tried below code but error is coming as 'Error converting data type nvarchar to datetime. The incorrect value "Tot_GPS_Cnt" is supplied in the PIVOT operator.'

我想将列转换为行。我是PIVOT的初学者。尝试下面的代码,但错误来自'错误将数据类型nvarchar转换为datetime。 PIVOT运算符中提供了错误值“Tot_GPS_Cnt”。

CREATE TABLE #tbl_Res1 
( 
  CallDate DATETIME, 
  Tot_GPS_Cnt INT, 
  Tot_GND_Cnt INT,  
  Per_Ratio NUMERIC(10,2) 
)

INSERT INTO #tbl_Res1 
SELECT '2015-04-24 00:00:00.000','40','26','65.00' UNION ALL
SELECT '2015-04-25 00:00:00.000','22','14','63.64' UNION ALL 
SELECT'2015-04-26 00:00:00.000','27','21','77.78' UNION ALL
SELECT'2015-04-27 00:00:00.000','41','23','56.10'

Source Table

Desired Output

I have tried with below query bu failing. Please help. Thanks in advance

我试过以下查询失败。请帮忙。提前致谢

SELECT CallDate=col, Tot_GPS_Cnt, Tot_GND_Cnt, Per_Ratio
FROM 
( select CallDate, col, value from #tbl_Res1
  cross apply 
  ( 
    SELECT 'Tot_GPS_Cnt',cast(Tot_GPS_Cnt as varchar(10)) UNION ALL
    SELECT 'Tot_GND_Cnt', cast(Tot_GND_Cnt as varchar(10)) UNION ALL
    SELECT 'Per_Ratio', cast(Per_Ratio as varchar(10))
  ) c (col,value)
) d 
PIVOT
(
max(value) for CallDate in ([Tot_GPS_Cnt], [Tot_GND_Cnt], [Per_Ratio])
) as piv

2 个解决方案

#1

The correct syntax for PIVOT is:

PIVOT的正确语法是:

PIVOT
(    
    <aggregation function>(<column being aggregated>)    
    FOR    
    [<column that contains the values that will become column headers>]

    IN ( [first pivoted column], [second pivoted column],

    ... [last pivoted column])

) AS <alias for the pivot table>

Hence, you have to place date values not [Tot_GPS_Cnt], [Tot_GND_Cnt], [Per_Ratio] in place of pivoted columns:

因此,您必须放置日期值而不是[Tot_GPS_Cnt],[Tot_GND_Cnt],[Per_Ratio]来代替旋转列:

SELECT CallDate=col, [2015-04-24], [2015-04-25], [2015-04-26], [2015-04-27]
FROM 
( select CallDate, col, value from #tbl_Res1
  cross apply 
  ( 
    SELECT 'Tot_GPS_Cnt',cast(Tot_GPS_Cnt as varchar(10)) UNION ALL
    SELECT 'Tot_GND_Cnt', cast(Tot_GND_Cnt as varchar(10)) UNION ALL
    SELECT 'Per_Ratio', cast(Per_Ratio as varchar(10))
  ) c (col,value)
) d 
PIVOT
(
max(value) for CallDate in ([2015-04-24], [2015-04-25], [2015-04-26], [2015-04-27])
) as piv

#2

Thank you @Giorgos Betsos. I just made dynamic solution. Just wanted to share with all.

谢谢@Giorgos Betsos。我刚刚做了动态解决方案。只是想与大家分享。

DECLARE @coldata VARCHAR(500);
DECLARE @sql VARCHAR(MAX);

SELECT @coldata = COALESCE(@coldata + '], [', '') + CONVERT(VARCHAR(10),calldate,110) 
FROM #tbl_Res1;
SELECT @coldata = '[' + @coldata +']';
SELECT @coldata;


SET @sql = 'SELECT CallDate=col, ' + @coldata + '
  FROM 
  ( SELECT CallDate, col, value FROM #tbl_Res1
    cross apply 
    ( 
      SELECT ''Tot_GPS_Cnt'', CAST(Tot_GPS_Cnt as VARCHAR(10)) UNION ALL
      SELECT ''Tot_GND_Cnt'', CAST(Tot_GND_Cnt as VARCHAR(10)) UNION ALL
      SELECT ''Per_Ratio'', CAST(Per_Ratio as VARCHAR(10))
    ) c (col,value)
  ) d 
  PIVOT
  (
    MAX(value) FOR CallDate IN (' + @coldata + ')
  ) as piv'

  SELECT @sql;

  EXECUTE (@SQL);