matrix or crosstab query

Hi friends,

I need to write a crosstab query in oracle like we do in Access. Here are my sample tables.

tblCat
---
sno (primary key)
cat_id

tblDistribution
------
area_id
dist_id (primary key)
dist_name

tblConsumer
-----
serviceno (primary key)
sno (references sno(tblCat))
area_id

I need the display as follows:

categories
----
dist_id & dist_name 1 2 3 4 5 ... (cat_ids...)
-------------------
id, name concatenated count(serviceno).....

ie., I want the No.of service for each category Id and for dist_id and dist_name.... The cat_id should not be duplicated.

Plz help me out of thios query as I need it badly. I've done it using crosstab query wizard in Access. here is the query

TRANSFORM Count(tb_consumer.Service_No) AS CountOfService_No
SELECT tb_distribution.Dist_Id + " - " +tb_distribution.Dist_Name AS Id_Name
FROM (tb_category INNER JOIN tb_consumer ON tb_category.SNO = tb_consumer.SNO) INNER JOIN tb_distribution ON tb_consumer.Area_Id = tb_distribution.Area_Id
GROUP BY tb_distribution.Dist_Id, tb_distribution.Dist_Name
PIVOT tb_category.Cat_Id;

If any one can, plz tell me how to write or convert the same to ORACLE or SQL Server.

Thanks in advance...!The process is very simple.

You need to create a pivot table :

field1 will appear as row header
field2 will appear as column header

Select field1, field2 into pivot from mytable where ....

This gives you a table with all the data you need to work with.

Now ...

Select field1, count(field2) as totalfield2,
sum(case field2 when 'xxxx' then 1 else 0) as colhdr1,
sum(case field2 when 'yyyy' then 1 else 0) as colhdr2,
sum(case field3 when 'zzzz' then 1 else 0) as colhdr3
from pivot
group by field1
order by field1

This should produce the results much like the Access CrossTab query. You can refine it to meet your requirements. I did not fully understand what you were looking for, but hopefully this will help.

you need to recreate the pivot table every time you run!

Matrix / Crosstab query in SQL Server

How can I produce a Matrix/Crosstab table using SQL Query Analyzer/SQL Server?

I have the following tables:

people(unique_identifier, employee_number, known_as_and_surname ... )
exitin(unique_identifier, parent_identifier, category, score ... )

Relationship: exitint.parent_identifier = people.unique_identifier which is a one-many relationship (one employee can have many records in exitint)

example data:
please see attachment (cant make a pretty table appear in the post...)

The zip file has an excel spreadsheet with what I have and want to produce as an example.

I want to make this in SQL (SQL Server 2000 I think)

I have been looking all over the internet for a solution but I just cant get one to work! :eek:

Thank you all in advance! <3The following code would create you a 2xN matrix. It should be straight forward to extend it so that that it would support the all your column by repeating the part from ,( to JOB for each or your columns

Select
employee_number
,(
select Exitin.score
from Exitin
where
Exitin.parent_identifier = People.Unique_identifier
and
Exitin.category = 'JOB'
) As JOB
from
People

This would solve your problem if you have predefined column headers. If they are not static you will have to create a dynamic #temp table with the required columns use an update statement to populate the data then do the request. Just be careful not to use too much resource as this method could become messy.

Hope this helps|||Server: Msg 512, Level 16, State 1, Line 9
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Hmm. That shouldn't happen...

I have jsut tried the following

SELECT people.employee_number, exitin.score AS JOB
FROM exitin, people
WHERE
exitin.parent_identifier = people.unique_identifier
AND
exitin.category = 'JOB'

Which produced what I think yours should have -
First column is employee_number

Second column heading: JOB
Second column values: score

Can I add, for example, another column with the heading 'T&D' that picked out all scores where category = 'T&D' ?|||The error you get would indicate that there are multiple entries in the Exitin table for People per category. Is that true?

Im asuming you would then want to add the score together (guess)

Select
employee_number
,(
select sum(Exitin.score)
from Exitin
where
Exitin.parent_identifier = People.Unique_identifier
and
Exitin.category = 'JOB'
) As JOB
from
People

If you dont want to add them you could always display just the first entry find and use a sort to only show most recent:

Select
employee_number
,(
select top 1 Exitin.score
from Exitin
where
Exitin.parent_identifier = People.Unique_identifier
and unique_identifier Decs
Exitin.category = 'JOB'
order by Unique_identifier
) As JOB
from
People

Good luck|||No person has the same category twice (supposedly anyway!)

I will try both the other methods and let you know, cheers mate!|||And there I was thinking it couldn't get any worse...

the sum function returned this error:
The sum or average aggregate operation cannot take a char data type as an argument.

the top 1 tells me:
Line 11: Incorrect syntax near '1'.

I'm stumped on this problem, really sorry.
I've been hitting at it for so long I've almost lost faith in it :P

Thanks for your help so far|||Oooh, ooh!
I tried using max() which appears to work...

Could you help me adding a second column please, not sure how the syntax would work for a second subquery.

:o|||Avoid using nested queries whenever possible. They are very inefficient.
Books Online gives an excellent example of how to construct a crosstab query using CASE statements. Yours might look something like this:

select employee_number,
sum(case category when 'JOB' then exitin.score else 0 end) as 'JOB',
sum(case category when 'TASK' then exitin.score else 0 end) as 'TASK',
sum(case category when 'HOBBY' then exitin.score else 0 end) as 'HOBBY',
.
.
.
sum(case category when 'OTHER' then exitin.score else 0 end) as 'OTHER'
from people
left outer join exitin on people.unique_identifier = exitin.parent_identifier
group by employee_number|||The case statement works - Thank you blindman

Thanks for all your help too Dwane.wilters

:D
I'm a happy programmer now :P|||Nice once blind man.

Much more efficient good call
:beer:|||Now to implement it for 25 categories :P

copy, paste, copy, paste, copy, paste, copy, paste, copy, paste, copy...

:D

Cheers guys :beer:|||OH GOD NO.

Server: Msg 245, Level 16, State 1, Line 23
Syntax error converting the varchar value '?' to a column of data type int.

I thought it might be because I was using the ampersand (&) character, so I removed them and it worked fine (then realising that because they were between apostrophes that it wouldn't matter anyway)

I'm still trying to debug this but if you have any ideas what may be casuing the problem let me know!

Here's my FULL statement

SELECT pwa_master.people.employee_number,

MAX(CASE pwa_master.exitin.category WHEN 'ABIL' THEN pwa_master.exitin.score ELSE 0 END) AS 'ABIL',
MAX(CASE pwa_master.exitin.category WHEN 'ATT' THEN pwa_master.exitin.score ELSE 0 END) AS 'ATT',
MAX(CASE pwa_master.exitin.category WHEN 'ATTEN' THEN pwa_master.exitin.score ELSE 0 END) AS 'ATTEN',
MAX(CASE pwa_master.exitin.category WHEN 'BENS' THEN pwa_master.exitin.score ELSE 0 END) AS 'BENS',
MAX(CASE pwa_master.exitin.category WHEN 'COMM' THEN pwa_master.exitin.score ELSE 0 END) AS 'COMM',
MAX(CASE pwa_master.exitin.category WHEN 'CON' THEN pwa_master.exitin.score ELSE 0 END) AS 'CON',
MAX(CASE pwa_master.exitin.category WHEN 'DIFRE' THEN pwa_master.exitin.score ELSE 0 END) AS 'DIFRE',
MAX(CASE pwa_master.exitin.category WHEN 'FLEX' THEN pwa_master.exitin.score ELSE 0 END) AS 'FLEX',
MAX(CASE pwa_master.exitin.category WHEN 'IND' THEN pwa_master.exitin.score ELSE 0 END) AS 'IND',
MAX(CASE pwa_master.exitin.category WHEN 'JOB' THEN pwa_master.exitin.score ELSE 0 END) AS 'JOB',
MAX(CASE pwa_master.exitin.category WHEN 'MAND' THEN pwa_master.exitin.score ELSE 0 END) AS 'MAND',
MAX(CASE pwa_master.exitin.category WHEN 'MANL' THEN pwa_master.exitin.score ELSE 0 END) AS 'MANL',
MAX(CASE pwa_master.exitin.category WHEN 'MOR' THEN pwa_master.exitin.score ELSE 0 END) AS 'MOR',
MAX(CASE pwa_master.exitin.category WHEN 'PROGR' THEN pwa_master.exitin.score ELSE 0 END) AS 'PROGR',
MAX(CASE pwa_master.exitin.category WHEN 'REASO' THEN pwa_master.exitin.score ELSE 0 END) AS 'REASO',
MAX(CASE pwa_master.exitin.category WHEN 'RWMA' THEN pwa_master.exitin.score ELSE 0 END) AS 'RWMA',
MAX(CASE pwa_master.exitin.category WHEN 'S&B' THEN pwa_master.exitin.score ELSE 0 END) AS 'S&B',
MAX(CASE pwa_master.exitin.category WHEN 'SAL' THEN pwa_master.exitin.score ELSE 0 END) AS 'SAL',
MAX(CASE pwa_master.exitin.category WHEN 'SIGLO' THEN pwa_master.exitin.score ELSE 0 END) AS 'SIGLO',
MAX(CASE pwa_master.exitin.category WHEN 'SUPNE' THEN pwa_master.exitin.score ELSE 0 END) AS 'SUPNE',
MAX(CASE pwa_master.exitin.category WHEN 'T&D' THEN pwa_master.exitin.score ELSE 0 END) AS 'T&D',
MAX(CASE pwa_master.exitin.category WHEN 'TEAM' THEN pwa_master.exitin.score ELSE 0 END) AS 'TEAM',
MAX(CASE pwa_master.exitin.category WHEN 'TIME' THEN pwa_master.exitin.score ELSE 0 END) AS 'TIME',
MAX(CASE pwa_master.exitin.category WHEN 'VALUE' THEN pwa_master.exitin.score ELSE 0 END) AS 'VALUE',
MAX(CASE pwa_master.exitin.category WHEN 'WCON' THEN pwa_master.exitin.score ELSE 0 END) AS 'WCON',
MAX(CASE pwa_master.exitin.category WHEN 'WLBAL' THEN pwa_master.exitin.score ELSE 0 END) AS 'WLBAL'

FROM pwa_master.people
LEFT OUTER JOIN pwa_master.exitin ON
pwa_master.people.unique_identifier = pwa_master.exitin.parent_identifier
GROUP BY pwa_master.people.employee_number

Thanks again everyone|||Run this to find records with invalid scores:

select *
from exitin
where isnumeric(score) = 0

or add this to your WHERE clause to exclude records with invalid scores

WHERE isnumeric(score) = 1|||Nevermind, found out some moron had put question marks in some of the fields.
Ignore the last post :P

-georgev