Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Question Unanswered: 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!

    Thank you all in advance! <3
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2007
    Posts
    6
    The 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

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    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
    Code:
    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' ?

  4. #4
    Join Date
    Jan 2007
    Posts
    6
    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
    Last edited by Dwane.wilters; 01-16-07 at 11:16.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    No person has the same category twice (supposedly anyway!)

    I will try both the other methods and let you know, cheers mate!

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    And there I was thinking it couldn't get any worse...

    the sum function returned this error:
    Code:
    The sum or average aggregate operation cannot take a char data type as an argument.
    the top 1 tells me:
    Code:
    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

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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.


  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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:
    Code:
    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
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The case statement works - Thank you blindman


    Thanks for all your help too Dwane.wilters


    I'm a happy programmer now :P

  10. #10
    Join Date
    Jan 2007
    Posts
    6
    Nice once blind man.

    Much more efficient good call

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Now to implement it for 25 categories :P

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



    Cheers guys

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    OH GOD NO.

    Code:
    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

    Code:
    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

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Run this to find records with invalid scores:
    Code:
    select	*
    from	exitin
    where	isnumeric(score) = 0
    or add this to your WHERE clause to exclude records with invalid scores
    Code:
    WHERE	isnumeric(score) = 1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Nevermind, found out some moron had put question marks in some of the fields.
    Ignore the last post :P

    -georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •