Pivot?

[David Mujica]

Do I understand that both your sites (A,B,C) and your attributes (X,Y,Z) are variable in number ? And you are counting some sort of occurrence ? My pivot tables show the months across the top, the salesman down the left and the number in the grid represents the number of orders closed in that month. Sound familiar ? Your Pivot might look something like this: select Salesman, [1] as Jan,[2] Feb,[3] Mar,[4] Apr,[5] May,[6] Jun, [7] Jul, [8] Aug, [9] Sep, [10] Oct, [11] Nov, [12] Dec from ( select salesman, OrderID, datepart(month,order_date) as month from orders where datepart(year,order_date) = 2010) AS SourceTable PIVOT (COUNT(OrderID) FOR month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PivotTable

[mark_w_]

[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) select * from Example

[Mycroft Holmes]

This article [^]may be useful, I use MAX (min will do just as well) for the aggregator.

Never underestimate the power of human stupidity RAH

[mark_w_]

I think the main problem is I need multiple aggregates (for X, Y and Z). I have this so far select * from Example pivot ( Max(X) for site in ([A],[B],[C]) ) as p

[Mycroft Holmes]

Not sure I understand your problem, are you saying you want 3 aggregates(X,Y,Z) for each type(columns) (A,B,C)

Never underestimate the power of human stupidity RAH

[mark_w_]

I think thats what I need, but not sure. Basically I want to rotate the returned data by 90 degrees

[Jorgen Andersson]

You need to unpivot first, or normalize it actually, into something similar to this format:

CREATE TABLE [dbo].[Example](
[Site] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Accident] [nvarchar] NOT NULL,
[Occations] [int] NOT NULL,
) ON [PRIMARY]

Either in the query or a temp table, but preferably it should be stored normalized in the database After that you can make the pivot the normal way

"When did ignorance become a point of view" - Dilbert

[Mycroft Holmes]

mark_w_ wrote:

I think thats what I need, but not sure.

In that case work through the article because that is EXACTLY what it does with 2 columns, just extend it to meet your requirements.

Never underestimate the power of human stupidity RAH

[mark_w_]

Thanks, you were right :) I now have it working.

[Jorgen Andersson]

You should consider normalizing the table. It would make it a lot easier in the future to add functionality. Consider this:

CREATE TABLE Incidents (
Site varchar
IncidentType varchar,
IncidentDate Date,
Incidentinfo varchar,
...
)

Then just make your pivot on Select site,incidenttype,count(*) as incidentcount from incidents You may also exchange site and incidenttype for IDs referencing tables holding info on sites and incidenttypes. If you add a site to your organisation you simply add a row in a table.

"When did ignorance become a point of view" - Dilbert