Crosstab in MSSQL - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Crosstab in MSSQL

Author

Topic

echilon
Starting Member

2 Posts

Posted - 2008-11-30 : 08:57:15

I've been using a crosstab query with MySQL which works perfectly, but now I need to implement the same thing in SQL Server 2005 and it's more difficult than I thought.

There's a graphic to illustrate the table layout at http://mi6.nu/remappedsql.png and the MySQL query which works is:

SELECT
MAX(IF(forename='john',surname,null)) AS john, MAX(IF(forename='lucy',surname,null)) AS lucy, MAX(IF(forename='jenny',surname,null)) AS jenny,
MAX(IF(forename='steve',surname,null)) AS steve,
MAX(IF(forename='richard',surname,null)) as richard, address
FROM tablename
GROUP BY address;

With SQL Server, I've tried using pivot, but I can't get the query to run. This is what I have so far

SELECT
MAX(CASE forename WHEN 'john' THEN surname ELSE null END) AS john,
MAX(CASE forename WHEN 'lucy' THEN surname ELSE null END) AS lucy,
MAX(CASE forename WHEN 'jenny' THEN surname ELSE null END) AS jenny,
MAX(CASE forename WHEN 'steve' THEN surname ELSE null END) AS steve,
MAX(CASE forename WHEN 'richard' THEN surname ELSE null END) as richard, address FROM tablename GROUP BY address;

I'm getting errors with the MAX function though.

Thanks for any help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-30 : 11:49:39

your sql server query looks fine. What was the error you got?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-30 : 11:52:19

Alternatively you could try this also


SELECT address,[John],[Lucy],[Jenny],[Steve],[Richard]
FROM
(SELECT forename,surname,address
 FROM Table)m
PIVOT (MAX(surname) FOR forename IN ([John],[Lucy],[Jenny],[Steve],[Richard]))p

echilon
Starting Member

2 Posts

Posted - 2008-12-01 : 13:51:13

Thanks, I got it working with


SELECT MAX(CASE WHEN forename = 'john'
                     THEN surname
                ELSE null END) AS john,
       MAX(CASE WHEN forename = 'lucy'
                     THEN surname
                ELSE null END) AS lucy,
       .....,
       address
FROM tablename
GROUP BY address

But I'll have a look at the pivot as an exercise.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 23:50:43

No problems you're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources