使用带有 SQL 的数据透视表将一些列转置为行 [英] Transpose some columns to rows using pivot with SQL

问题描述

MS SQL Server 2012

MS SQL Server 2012

我有一个名为 indexrows

name    displayname propertyvalue
abc      $row1        agg
abc      $row2        spx
abc      $row3        qqq
def      $row1        spx
def      $row2        qqq

我想将这些结果转置为如下所示.

I would like to transpose these results to look like this.

name    $row1   $row2   $row3
abc      agg    spx    qqq
def      spx    qqq 

我尝试了以下查询但没有成功.我收到此错误

I tried the following query without success. I get this error

消息 156，级别 15，状态 1，第 10 行关键字for"附近的语法不正确.

Msg 156, Level 15, State 1, Line 10 Incorrect syntax near the keyword 'for'.

select * 
from (
select 
name,propertyvalue, displayname
from indexrows
) a
PIVOT 
(
propertyvalue
for [displayname] in ('$row1', '$row2', '$row3')
) as pivot

感谢任何帮助.

推荐答案

您的查询有一些问题.

首先，您在 PIVOT 上缺少聚合函数.您需要围绕 propertyvalue 进行聚合.

First, you are missing an aggregate function on your PIVOT. You need an aggregate around propertyvalue.

其次，您需要用方括号而不是单引号将 $row1 等括起来.

Second, you need to surround the $row1, etc with square brackets not single quotes.

第三，我会为 as pivot

因此代码将是:

select * 
from 
(
  select name, propertyvalue, displayname
  from indexrows
) a
pivot
(
  max(propertyvalue)
  for [displayname] in ([$row1], [$row2], [$row3])
) piv;

参见 SQL Fiddle with Demo

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