Crosstab with MSSQL

[Echilon]

I posted a couple of weeks ago regarding a crosstab in MySQL (

http://www.tek-tips.com/viewthread.cfm?qid=1513428

). The solution I found worked perfectly, but now I need to implement the same thing in SQL Server 2008 and it's more difficult than I though.

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.

 

[bborissov]

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

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Echilon]

Thanks. That seems to have fixed a few errors, but the real query is still giving me problems. The 'forename' field is actually called 'languages' and the 'surname' is called 'translation'.

When I try the query:

SELECT
MAX(CASE WHEN language = 'de-DE' THEN translation ELSE 0 END) AS 'de-DE', 
MAX(CASE WHEN language = 'en-GB' THEN translation ELSE 0 END) AS 'en-GB',
elements.id, applicationid, bundlename, elementname, elements.translationid, elements.comment AS elementcomment, translationcomments.comment as translationcomment
FROM elements 
	INNER JOIN translations ON translations.translationid = elements.translationid 
	LEFT JOIN translationcomments ON translations.translationid = translationcomments.translationid 
GROUP BY elements.translationid 
ORDER BY bundlename, elementname

I get "Column 'elements.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." I've notived that when you design a query in the SQL Server management studio, all fields are added to the group by clause, but this doesn't work either.

Any ideas?

 

[bborissov]

When you use aggregate functions like MAX(), MIN, SUM() etc. ALL fields which is not involved in these functions must be in the GROUP BY statement:

SELECT
MAX(CASE WHEN language = 'de-DE' THEN translation ELSE 0 END) AS 'de-DE',
MAX(CASE WHEN language = 'en-GB' THEN translation ELSE 0 END) AS 'en-GB',
elements.id, applicationid, bundlename, elementname, elements.translationid, elements.comment AS elementcomment, translationcomments.comment as translationcomment
FROM elements
    INNER JOIN translations ON translations.translationid = elements.translationid
    LEFT JOIN translationcomments ON translations.translationid = translationcomments.translationid
GROUP BY elements.id,
         applicationid,
         bundlename,
         elementname,
         elements.translationid,
         elements.comment,
         translationcomments.comment
ORDER BY bundlename, elementname

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Echilon]

Thanks that worked a charm. =)