SQL Server Query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

SQL Server Query

Author

Topic

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2009-10-08 : 16:41:49

Hi,
I've a table structure as shown below

Table Vendor:
Id Name
1 ABC
2 XYZ
3 PQR

Table License:
Vendor_Id Code Status
1 M Active
2 L Cancelled
3 K Fresh
1 L Cancelled
2 M Active
2 K Active

Now, I want to display the report in tabular format as shown below:
Vendor Fresh Active Cancelled
ABC 0 1 1
XYZ 0 2 1
PQR 1 0 0
Total 1 3 2

Can any one provide me the query to build the report in above format? I tried a lot but couldn't succeed in one shot query. I'll appreciate if you can guide me!

Thanks!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-10-08 : 16:53:04

this?

select a.[Name], b.[Fresh],b.[Active],b.[Cancelled]
from Vendor a
inner join
(
select Vendor_Id,
sum(case [Status] when 'Fresh' then 1 else 0 end) as [Fresh],
sum(case [Status] when 'Active' then 1 else 0 end) as [Active],
sum(case [Status] when 'Cancelled' then 1 else 0 end) as [Cancelled]
from License
group by Vendor_Id
) b
on a.Id = b.Vendor_Id

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2009-10-08 : 17:08:42

MIND BLOWING!!!!!!!!!!

THIS FORUM IS GREAT!!!!

THANKS A MILLION TO vijayisonly!!!!!

THANKS!!

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2009-10-08 : 17:12:20

Hey vijayisonly,
In the output, can we also add Total like this below:

Total 12 22 44

Will it be possible within the same SQL?
Thanks!

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-09 : 02:15:37

select
a.[Name], b.[Fresh],b.[Active],b.[Cancelled]
from
Vendor a
inner join
(
select
Vendor_Id,
sum(case [Status] when 'Fresh' then 1 else 0 end) as [Fresh],
sum(case [Status] when 'Active' then 1 else 0 end) as [Active],
sum(case [Status] when 'Cancelled' then 1 else 0 end) as [Cancelled]
from
License
group by
Vendor_Id
) b
on
a.Id = b.Vendor_Id
COMPUTE SUM(b.[Fresh])
COMPUTE SUM(b.[Active])
COMPUTE SUM(b.[Cancelled])

--Or if you want the total in the same recordset as a single row

select
a.[Name], b.[Fresh],b.[Active],b.[Cancelled]
from
Vendor a
inner join
(
select
Vendor_Id,
sum(case [Status] when 'Fresh' then 1 else 0 end) as [Fresh],
sum(case [Status] when 'Active' then 1 else 0 end) as [Active],
sum(case [Status] when 'Cancelled' then 1 else 0 end) as [Cancelled]
from
License
group by
Vendor_Id
) b
on
a.Id = b.Vendor_Id
union all
Select
'TOTAL' AS [Name],
sum(case [Status] when 'Fresh' then 1 else 0 end) as [Fresh],
sum(case [Status] when 'Active' then 1 else 0 end) as [Active],
sum(case [Status] when 'Cancelled' then 1 else 0 end) as [Cancelled]
from
License

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2009-10-12 : 17:33:42

Great forum! Thanks a lot sanoj_av!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources