Crosstab/Pivot from two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Crosstab/Pivot from two tables

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2006-03-16 : 19:31:20

Hello...

I have two tables, one is the "live" view and the other is the audit table. Basically, the two tables share the same schema. Whenever an operation attempts to update a row, the existing row is first inserted into the audit table and then the row in the "live" table is updated.

What I am trying to do, is create a cross-tab or pivot table that would include the data from both tables.

For example, I can get a cross-tab with the current data with:
SELECT p.Location,
Count(P.EmplID) as [EmpCount],
SUM(CASE P.Status WHEN 'Status1' THEN 1 ELSE 0 END) AS Stat1,
SUM(CASE P.Status WHEN 'Status2' THEN 1 ELSE 0 END) AS Stat2,
SUM(CASE P.Status WHEN 'Status3' THEN 1 ELSE 0 END) AS Stat3,
SUM(CASE P.Status WHEN 'Status4' THEN 1 ELSE 0 END) AS Stat4,
SUM(CASE P.Status WHEN 'Status5' THEN 1 ELSE 0 END) AS Stat5
FROM Emp P
GROUP BY p.Location
order by location

The result looks something like: (abbreviated for the sake of clarity)
Location | EmpCount | Stat1 | Stat2
Building A 50 30 20
Building B 12 11 1
Building Z 120 80 40

But, I cannot figure out how to Join my Audit_Emp table into this, so I can get an accumulated result.

So...an EmplID of 1002 will exist once in the Emp table...but could exist 2,3...heck even 10 times in the Audit_Emp table.

I tried UNION...but the GROUP BY stuff would not work.

If anyone could help or point out some place for me to look that would be great.

Thanks
- will

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-16 : 20:33:33

Can you post some sample data and the expected result ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-17 : 00:24:40

Post the join code you used

Madhivanan

Failing to plan is Planning to fail

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2006-03-17 : 17:27:56

Here is what the data looks like (there are more columns here...but none that I care about for the summary of data)

Emp table
EmpID, Location, Status
1001, Building A, Arrived
1003, Building A, Departed
1005, Building B, Available
1007, Building C, Busy

Audit_Emp
EmpID, Location, Status
1001, Building B, Departed
1001, Building C, Busy
1003, Building B, Departed
1005, Building A, Departed
1005, Building D, Busy
1001, Building C, Available
1007, Building A, Departed

End Result:
Location | Total | Arrived | Available | Busy | Departed
Building A | 4 | 1 | 0 | 0 | 3
Building B | 3 | 0 | 1 | 0 | 2
Building C | 3 | 0 | 1 | 2 | 0
Building D | 1 | 0 | 0 | 1 | 0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-17 : 20:07:29

[code]create table #Emp
(
EmpID int,
Location varchar(100),
Status varchar(20)
)
insert into #Emp
select 1001, 'Building A', 'Arrived' union all
select 1003, 'Building A', 'Departed' union all
select 1005, 'Building B', 'Available' union all
select 1007, 'Building C', 'Busy'

create table #Audit_Emp
(
EmpID int,
Location varchar(100),
Status varchar(20)
)
insert into #Audit_Emp
select 1001, 'Building B', 'Departed' union all
select 1001, 'Building C', 'Busy' union all
select 1003, 'Building B', 'Departed' union all
select 1005, 'Building A', 'Departed' union all
select 1005, 'Building D', 'Busy' union all
select 1001, 'Building C', 'Available' union all
select 1007, 'Building A', 'Departed'

select Location, sum(Total) as Total, sum(Arrived) as Arrived, sum(Available) as Available, sum(Busy) as Busy, sum(Departed) as Departed
from
(
select Location, count(*) as Total,
sum(case when Status = 'Arrived' then 1 else 0 end) as Arrived,
sum(case when Status = 'Available' then 1 else 0 end) as Available,
sum(case when Status = 'Busy' then 1 else 0 end) as Busy,
sum(case when Status = 'Departed' then 1 else 0 end) as Departed
from #Emp e
group by Location
union all
select Location, count(*) as Total,
sum(case when Status = 'Arrived' then 1 else 0 end) as Arrived,
sum(case when Status = 'Available' then 1 else 0 end) as Available,
sum(case when Status = 'Busy' then 1 else 0 end) as Busy,
sum(case when Status = 'Departed' then 1 else 0 end) as Departed
from #Audit_Emp e
group by Location
) as t
group by Location[/code]

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-20 : 05:51:24

This yields the same result in this example...


select	Location, count(*) as Total,
	sum(case when Status = 'Arrived'   then 1 else 0 end) as Arrived,
	sum(case when Status = 'Available' then 1 else 0 end) as Available,
	sum(case when Status = 'Busy'      then 1 else 0 end) as Busy,
	sum(case when Status = 'Departed'  then 1 else 0 end) as Departed
from
    (select * from #Emp union all select * from #Audit_Emp) a
group by
    location

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2006-03-20 : 15:07:08

Thanks...the last two work fantastic.
- will

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources