SQL Pivot问题-多个聚合? [英] SQL Pivot Problem - multiple aggregates?

问题描述

我有以下返回的数据，请购买一个简单的SQL查询.站点的数量可以更改，但是X，Y，Z是固定的(它们是不同类型的事故，并且所存储的数据表示发生的次数)

I have the following data returned buy a simple SQL query. The Number of sites could change, but X,Y,Z are fixed (they are different types of accidents, and the data stored represents the number of occurances)

| Site | X | Y | Z |
--------------------
   A      1   2   3
   B      4   5   6
   C      7   8   9

我需要将其设置为以下格式

I need to get it to the following format

| A | B | C |
--------------
   1   4   7
   2   5   8
   3   6   9

我到目前为止有这个

select * 
from Example
pivot 
(
Max(X)
for site in ([A],[B],[C])
) as p

但是我认为我需要多个集合(用于X，Y和Z).

But I think I need multiple aggregates (for X, Y and Z).

这是创建基本数据的快速脚本

Here is a quick script to create the base data

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Example](
     [Site] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
     [X] [int] NOT NULL,
     [Y] [int] NOT NULL,
     [Z] [int] NOT NULL
) ON [PRIMARY]

insert into Example(Site, X,Y,Z) Values ('A',1,2,3)
insert into Example(Site, X,Y,Z) Values ('B',4,5,6)
insert into Example(Site, X,Y,Z) Values ('C',7,8,9)

当我被困住时，任何帮助都真的很受欢迎！

Any help really welcome as I am stuck!

标记

推荐答案

您需要先对数据进行PIVOT处理，然后再对事故现场重新进行数据处理-像这样:

You need to UNPIVOT your data, before rePIVOTing it about the accident site - like so:

select * 
from Example
unpivot 
(
numbers 
for type in (x,y,z)
) as p
pivot 
(
Max(numbers)
for site in ([A],[B],[C])
) as q

这篇关于SQL Pivot问题-多个聚合?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！