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
I have this so far
select *
from Example
pivot
(
Max(X)
for site in ([A],[B],[C])
) as p
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!
Mark