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

Matrix

I've produce a report using a matrix.

Within that matrix I want to navigate to a subreport, however this only applies to certain columns in the matrix and within only when a cell contains a certain value.

Can anybody tell me how to reference individual matrix columns and whether the above is possible?

It sounds like you want a "drillthrough" to another report rather than a subreport.

You can use an expression to dynamically determine the drillthrough target report name, e.g. based on a value in the matrix cell or based on a certain grouping scope. For instance, add a drillthrough navigation action on a matrix cell with an expression-based drillthrough report name: =iif(InScope("Matrix_ColumnGroupName"), "DrillthroughReportName", Nothing)

If the expression for the report name evaluates to Nothing, no drillthrough link will be shown.

-- Robert

Matrices in reporting services

Hello All,

I am attempting to produce a Summary report that will detail all financial losses incurred by a company. These losses are seperated into categories and each category has two types, direct and indirect. To date I have employed the use of multiple matrices.

The problem is that if the company has incurred no losses for a specific category then this will cause the matrix to automatically cater for the lack of this column. However I want this to show up regardless of there being data...

I cannot employ the use of tables...as it simply does not work!

Does anyone have any suggestions? Ideas?

All would be appreciated!!

Thanks

JrDevRon,

You could return "Zeros" or Blank space in your SQL where no losses occur for specific categories. This is ensure that you always return all categories regardless.

Ham

|||

Hi Ham,

Thanks for the reply!

I have this implemented already and this works fine when at least one of the cells in the same column/row has been populated. If there is no data for a specific column then the whole thing disappears regardless...this is something I do not want.

Right now I have it this way and giving me a subtotal, I also then have another row detailing the recovered amounts which apply to my categories below my subtotal, obviously due to restrictions I have to use a second matrix for this information. I then subtract the corresponding information from the subtotal. But due to the above problem these columns do not always line up on the report...

I know this sounds convoluted and more complex than it probably needs to be but I would be appreciative of any help...

Thank you all!!

master.INFORMATION_SCHEMA.SCHEMATA

Is is just me or does this query produce the wrong results?
SELECT CATALOG_NAME FROM master.INFORMATION_SCHEMA.SCHEMATA
I have a MSDE sp3a installation, and this query does not include my user
database in the resut set.
hi,
Wozza wrote:
> Is is just me or does this query produce the wrong results?
> SELECT CATALOG_NAME FROM master.INFORMATION_SCHEMA.SCHEMATA
> I have a MSDE sp3a installation, and this query does not include my
> user database in the resut set.
yes... it seems to work only when running in the user's database context,
and only for the specifi user's database..
CREATE DATABASE a
CREATE DATABASE b
if run on "b" database context it returns
USE b
SELECT CATALOG_NAME FROM master.INFORMATION_SCHEMA.SCHEMATA
CATALOG_NAME
master
tempdb
model
msdb
b
(5 row(s) affected)
seems to be a problem in the
WHERE
a_cha.type = 1001 -- type is charset
AND a_cha.id = convert(tinyint, DatabasePropertyEx(db.name, 'sqlcharset'))
filter of the view,
select
db.name as CATALOG_NAME
,USER_NAME() as SCHEMA_NAME
,USER_NAME() as SCHEMA_OWNER
,convert(sysname, NULL) as DEFAULT_CHARACTER_SET_CATALOG
,convert(sysname, NULL) as DEFAULT_CHARACTER_SET_SCHEMA
,a_cha.name as DEFAULT_CHARACTER_SET_NAME
FROM
master.dbo.sysdatabases db,
master.dbo.syscharsets a_cha --charset/1001, not sortorder.
WHERE
a_cha.type = 1001 -- type is charset
AND a_cha.id = convert(tinyint, DatabasePropertyEx(db.name, 'sqlcharset'))
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||ok... SQL Server MVP Jasper Smith expanded this reporting it a a "known
issue" of that ANSI view, depending on the autoclose property (default on
MSDE) of the database(s), that causes them to not appear in the
INFORMATION_SCHEMA.SCHEMATA view
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply