Help needed with this problem........

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)

Help needed with this problem........

Author

Topic

raaj
Posting Yak Master

129 Posts

Posted - 2008-12-15 : 19:41:41

Hi Guys,
This is the way I would like my output to look like (or anything similar to that is also fine)
State MN NJ NY TX VA -(display all states)
TotalDept 9 8 20 6 23 --(Total sum of depts)
FoodDept --(individual count)
Travel
Transport
....so on

TotalPracGrp 6 14 8 8 8
AXA
IIR
CRT
...so on

TotalDelivery 3 7 4 4 4
EDC
ALS
BHR
..so on

TotalUrgency

12 24 24 22 14
Level1
Level2
Level3
...so on

So ,basically I want to display the totals of dept,pracGroup,Delivery,UrgencyLevel. Then Out of those How many were
FoodDept,Travel,Transport......How many were AXA,IIR...so on???
by State

The below is the sample data query....
declare
@TempTable table (State char(10),dept int,PracGroup int,Delivery int,UrgencyLevel int)

insert into @TempTable values('NJ',1,2,1,1)
insert into @TempTable values('NJ',1,2,1,2)
insert into @TempTable values('NJ',1,2,1,3)
insert into @TempTable values('NJ',1,2,1,4)
insert into @TempTable values('NJ',2,2,1,5)
insert into @TempTable values('NJ',1,2,1,6)
insert into @TempTable values('TX',3,2,1,8)
insert into @TempTable values('TX',1,2,1,9)
insert into @TempTable values('TX',1,2,1,1)
insert into @TempTable values('VA',4,2,1,2)
insert into @TempTable values('VA',1,2,1,3)
insert into @TempTable values('VA',5,2,1,4)
insert into @TempTable values('NY',1,2,1,5)
insert into @TempTable values('NY',1,2,1,6)
insert into @TempTable values('NY',6,2,1,8)
insert into @TempTable values('MN',1,2,1,9)
insert into @TempTable values('MN',1,2,1,1)
insert into @TempTable values('MN',7,2,1,2)
insert into @TempTable values('NJ',1,2,1,3)
insert into @TempTable values('TX',1,2,1,4)
insert into @TempTable values('NY',12,2,1,5)
insert into @TempTable values('VA',13,2,1,5)

This is the sample raw data...and from the above query the numbers represent respective names...so I have used case statement to make numbers as names......so far I have tried this ....But was not getting idea how to get the actual output????
SELECT State,
-- (CASE dept When '1' THEN 'FOODDEPT'
-- WHEN '2' THEN 'TRAVEL'
-- WHEN '3' THEN 'TRANSPORT'
-- WHEN '4' THEN 'HEALTH'
-- WHEN '5' THEN 'DEPT5'
-- WHEN '6' THEN 'DEPT6'
-- WHEN '7' THEN 'DEPT7'
-- WHEN '8' THEN 'DEPT8'
-- WHEN '9' THEN 'CARE'
-- WHEN '10'THEN 'SPORTS'
-- WHEN '11'THEN 'DEPT11'
-- WHEN '12'THEN 'DEPT12'
-- WHEN '13'THEN 'DEPT13'
-- --Else 'Null'
-- END) AS 'dept Status',
SUM(dept) As 'Totaldept',
-- (CASE PracGroup When '1' THEN 'AXA'
-- WHEN '2' THEN 'IIR'
-- WHEN '3' THEN 'CRT'
-- WHEN '4' THEN 'AFS'
-- WHEN '5' THEN 'Other'
-- WHEN '6' THEN 'UTV'
-- WHEN '7' THEN 'CRT-I'
-- WHEN '8' THEN 'Multiple'
-- WHEN '9' THEN 'Unknown'
-- END) AS 'PracGroup',
SUM(PracGroup) As 'TotalPracGroup',
-- (case Delivery when '1' then 'EDC'
-- when '1' then 'ALS'
-- when '2' then 'BHR'
-- when '8' then 'Multiple'
-- when '9' then 'Unknown'
-- End) As 'Delivery',
SUM(Delivery) As 'TotalDelivery',
-- (Case UrgencyLevel when '1' then 'Level 1'
-- when '2' then 'Level 2'
-- when '3' then 'Level 3'
-- when '4' then 'Level 4'
-- when '5' then 'N/A'
-- when '6' then 'NCR'
-- when '8' then 'Multiple Resp.'
-- when '9' then 'Unknown'
-- End) As 'UrgencyLevel',
SUM(UrgencyLevel) As 'TotalUrgencyLevel'
--sum(dept), sum(PracGroup),sum(Delivery),sum(UrgencyLevel)
from @TempTable
GROUP BY State

I am using SQL SERVER 2000
Any suggestions or help would be really appreciable...I tried in different ways but was unable to get the actual out which I am looking for or (something similar to tht)

Thanks,
Raaj......

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-15 : 20:01:09

Are you looking for this one?

declare @TempTable table (State char(10),dept int,PracGroup int,Delivery int,UrgencyLevel int)

insert into @TempTable values('NJ',1,2,1,1)
insert into @TempTable values('NJ',1,2,1,2)
insert into @TempTable values('NJ',1,2,1,3)
insert into @TempTable values('NJ',1,2,1,4)
insert into @TempTable values('NJ',2,2,1,5)
insert into @TempTable values('NJ',1,2,1,6)
insert into @TempTable values('TX',3,2,1,8)
insert into @TempTable values('TX',1,2,1,9)
insert into @TempTable values('TX',1,2,1,1)
insert into @TempTable values('VA',4,2,1,2)
insert into @TempTable values('VA',1,2,1,3)
insert into @TempTable values('VA',5,2,1,4)
insert into @TempTable values('NY',1,2,1,5)
insert into @TempTable values('NY',1,2,1,6)
insert into @TempTable values('NY',6,2,1,8)
insert into @TempTable values('MN',1,2,1,9)
insert into @TempTable values('MN',1,2,1,1)
insert into @TempTable values('MN',7,2,1,2)
insert into @TempTable values('NJ',1,2,1,3)
insert into @TempTable values('TX',1,2,1,4)
insert into @TempTable values('NY',12,2,1,5)
insert into @TempTable values('VA',13,2,1,5)

SELECT State,
(CASE dept When '1' THEN 'FOODDEPT'
WHEN '2' THEN 'TRAVEL'
WHEN '3' THEN 'TRANSPORT'
WHEN '4' THEN 'HEALTH'
WHEN '5' THEN 'DEPT5'
WHEN '6' THEN 'DEPT6'
WHEN '7' THEN 'DEPT7'
WHEN '8' THEN 'DEPT8'
WHEN '9' THEN 'CARE'
WHEN '10'THEN 'SPORTS'
WHEN '11'THEN 'DEPT11'
WHEN '12'THEN 'DEPT12'
WHEN '13'THEN 'DEPT13'
Else 'Null'
END) AS 'dept Status',
(CASE PracGroup When '1' THEN 'AXA'
WHEN '2' THEN 'IIR'
WHEN '3' THEN 'CRT'
WHEN '4' THEN 'AFS'
WHEN '5' THEN 'Other'
WHEN '6' THEN 'UTV'
WHEN '7' THEN 'CRT-I'
WHEN '8' THEN 'Multiple'
WHEN '9' THEN 'Unknown'
END) AS 'PracGroup',
(case Delivery when '1' then 'EDC'
when '1' then 'ALS'
when '2' then 'BHR'
when '8' then 'Multiple'
when '9' then 'Unknown'
End) As 'Delivery',
(Case UrgencyLevel when '1' then 'Level 1'
when '2' then 'Level 2'
when '3' then 'Level 3'
when '4' then 'Level 4'
when '5' then 'N/A'
when '6' then 'NCR'
when '8' then 'Multiple Resp.'
when '9' then 'Unknown'
End) As 'UrgencyLevel',
sum(dept)As 'Totaldept', sum(PracGroup)AS 'PracGroup',sum(Delivery)as 'Totaldeliveries',sum(UrgencyLevel)As 'TotalUrgencyLevel'
from @TempTable
GROUP BY State,(CASE dept When '1' THEN 'FOODDEPT'
WHEN '2' THEN 'TRAVEL'
WHEN '3' THEN 'TRANSPORT'
WHEN '4' THEN 'HEALTH'
WHEN '5' THEN 'DEPT5'
WHEN '6' THEN 'DEPT6'
WHEN '7' THEN 'DEPT7'
WHEN '8' THEN 'DEPT8'
WHEN '9' THEN 'CARE'
WHEN '10'THEN 'SPORTS'
WHEN '11'THEN 'DEPT11'
WHEN '12'THEN 'DEPT12'
WHEN '13'THEN 'DEPT13'
Else 'Null'
END) ,
(CASE PracGroup When '1' THEN 'AXA'
WHEN '2' THEN 'IIR'
WHEN '3' THEN 'CRT'
WHEN '4' THEN 'AFS'
WHEN '5' THEN 'Other'
WHEN '6' THEN 'UTV'
WHEN '7' THEN 'CRT-I'
WHEN '8' THEN 'Multiple'
WHEN '9' THEN 'Unknown'
END) ,
(case Delivery when '1' then 'EDC'
when '1' then 'ALS'
when '2' then 'BHR'
when '8' then 'Multiple'
when '9' then 'Unknown'
End) ,
(Case UrgencyLevel when '1' then 'Level 1'
when '2' then 'Level 2'
when '3' then 'Level 3'
when '4' then 'Level 4'
when '5' then 'N/A'
when '6' then 'NCR'
when '8' then 'Multiple Resp.'
when '9' then 'Unknown'
End)

raaj
Posting Yak Master

129 Posts

Posted - 2008-12-15 : 21:44:13

Hi Sodeep,
Thanx for your
Is it possible in SQL SERVER 2000 that I can bring the output to the form I have mentioned below(because I need to produce them as a report in excel)....
and what about Individual counts????The query which you have sended is giving me total...and now I also want from tht Total How many were FoodDept,Travel,Transport.....etc.......and so on.......

State MN NJ NY TX VA
TotalDept
FoodDept
Travel
Transport
.....so on
TotalPracGroup
AXA
IIR
CRT
AFS
....so on
TotalDelivery
EDC
ALS
BHR
....so on
TotalUrgencyLevel
Level 1
Level 2
Level 3
Level 4
...so on

Like for TotalDept it shud be Total i.e Sum for MN,NJ,NY etc....
and in the next line there shud be Count i.e Out of Total (above mentioned) How many were FoodDept,How many were Travel, How many were Health etc.....and so on........
I think this is some wht related to interchanging rows and columns....but I am not too sure....

Thnx.....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 23:56:39

this is purely a formatting issue and should be done at your front end application. this seems like for a reporting purpose and this type of formats can be easily generated in reporting tools like sql reporting services. which tool are you using?

raaj
Posting Yak Master

129 Posts

Posted - 2008-12-16 : 00:38:40

Hi Visakh,
Thanks for your reply.....
Are there any other Free Reporting tools (other than SSRS) where I can download it and then generate a report using that sql query.....

Thanks...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 01:03:06

quote:
Originally posted by raaj

Hi Visakh,
Thanks for your reply.....
Are there any other Free Reporting tools (other than SSRS) where I can download it and then generate a report using that sql query.....

Thanks...

this is a free tool

http://www.freereporting.com/rdPage.aspx?rdReport=FreeReporting

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources