Query Problem

[bazooka]

I have a single table in MS SQL Server with the following fields.

FacName, Course, section, term,
Resp#1,Resp#2,Resp#3

The combination of FacName,course,section and term is unique.

Resp#1, Resp#2 etc can take the values 3,2,1.

Here 3 ==> Agree (A)
2 ==> Neutral (N)
1 ==> Disagree (D)

I have to create a query to find the count of 3s,2s and 1s for each unique FacName,course,section, term.

So the final output will have fields
facname,course,section,term,
Resp#1_A,Resp#1_N,Resp#1_D,
Resp#2_A,Resp#2_N,Resp#2_D,
Resp#3_A,Resp#3_N,Resp#3_D

Where for example Resp#1_A ==> Number of 3s for Resp#1 for that unique facname,course,section,term combination. Similarly Resp#1_N ==> Number of 2s for Resp#1 and Resp#3_D ==> number of 1s for Resp#3

I created a temporary table with the destination fields used a cursor to go pass each record and update the temp table to produce the final output. But because the table has millions of records it is taking a lot of time.

Is there any means by which this could be done with a normal query by using Joins and derived tables.

Thanks for any help.

[MAK]

please post the DDL and insert statements and the result you needed?

[bazooka]

The following is the DDL

Source table-(Table Name:Eval)
Fields
facname nvarchar(29)
course nvarchar(4)
section nvarchar(3)
term nvarchar(3)
Resp#1 char(1)
Resp#2 char(1)
Resp#3 char(1)

Destination table-Table Name:NewEval
facname nvarchar(29)
course nvarchar(4)
section nvarchar(3)
term nvarchar(3)
Resp#1_A varchar(4)
Resp#1_N varchar(4)
Resp#1_D varchar(4)
Resp#2_A varchar(4)
Resp#2_N varchar(4)
Resp#2_D varchar(4)
Resp#3_A varchar(4)
Resp#3_N varchar(4)
Resp#3_D varchar(4)


I have attached the example Input and Output format in a txt file

[MAK]

you can do using cross tab query...
--please see below example

--drop table Eval
create table Eval (
facname nvarchar(29),course nvarchar(4),section nvarchar(3),term nvarchar(3),
Resp#1 char(1),Resp#2 char(1),Resp#3 char(1))

create table NewEval(
facname nvarchar(29),course nvarchar(4),section nvarchar(3),
term nvarchar(3),Resp#1_A varchar(4),Resp#1_N varchar(4),Resp#1_D varchar(4),
Resp#2_A varchar(4),Resp#2_N varchar(4),Resp#2_D varchar(4),Resp#3_A varchar(4),
Resp#3_N varchar(4),Resp#3_D varchar(4))

insert into Eval select 'Daniel','ME','001','156','3','3','1'
insert into Eval select 'Mark','PHY','012','156','2','3',NULL
insert into Eval select 'Antony','CHEM','001','156','3','1','3'
insert into Eval select 'Mark','PHY','012','156','3','3','2'
insert into Eval select 'Daniel','ME','001','156','1','2','1'


--select * from eval

SELECT facname, max(course) as course ,max(section) as section ,max(term) as term,
sum(CASE Resp#1 WHEN 3 THEN 1 ELSE 0 END) AS Resp#1_A,
sum(CASE Resp#1 WHEN 2 THEN 1 ELSE 0 END) AS Resp#1_N,
sum(CASE Resp#1 WHEN 1 THEN 1 ELSE 0 END) AS Resp#1_D,
sum(CASE Resp#2 WHEN 3 THEN 1 ELSE 0 END) AS Resp#2_A,
sum(CASE Resp#2 WHEN 2 THEN 1 ELSE 0 END) AS Resp#2_N,
sum(CASE Resp#2 WHEN 1 THEN 1 ELSE 0 END) AS Resp#2_D,
sum(CASE Resp#3 WHEN 3 THEN 1 ELSE 0 END) AS Resp#3_A,
sum(CASE Resp#3 WHEN 2 THEN 1 ELSE 0 END) AS Resp#3_N,
sum(CASE Resp#3 WHEN 1 THEN 1 ELSE 0 END) AS Resp#3_D
FROM Eval
GROUP BY facname

[MAK]

if you like to group by other columns ... then



SELECT facname, course,section,term,
sum(CASE Resp#1 WHEN 3 THEN 1 ELSE 0 END) AS Resp#1_A,
sum(CASE Resp#1 WHEN 2 THEN 1 ELSE 0 END) AS Resp#1_N,
sum(CASE Resp#1 WHEN 1 THEN 1 ELSE 0 END) AS Resp#1_D,
sum(CASE Resp#2 WHEN 3 THEN 1 ELSE 0 END) AS Resp#2_A,
sum(CASE Resp#2 WHEN 2 THEN 1 ELSE 0 END) AS Resp#2_N,
sum(CASE Resp#2 WHEN 1 THEN 1 ELSE 0 END) AS Resp#2_D,
sum(CASE Resp#3 WHEN 3 THEN 1 ELSE 0 END) AS Resp#3_A,
sum(CASE Resp#3 WHEN 2 THEN 1 ELSE 0 END) AS Resp#3_N,
sum(CASE Resp#3 WHEN 1 THEN 1 ELSE 0 END) AS Resp#3_D
FROM Eval
GROUP BY facname, course,section,term

[bazooka]

Thank you MAK.. I cannot admire you enough.. the idea never struck me..your method has reduced the running time by a large extent.. thanks a bunch.

[MAK]