pivot table sql 2000 case when statement

SELECT     contactdetails_id, CASE WHEN contactdetials_typeid = 8 THEN contactdetails_value as website, CASE WHEN contactdetials_typeid = 7 THEN contactdetails_value as mobile
FROM         tbl_contactdetails
GROUP BY contactdetails_id

why wont this work?

I am trying to get a list of values (contactdetails_value is varchar) into a pivot table
so that the mobile and website associated with the contactid is on the same row

help




Chris MichalczukConsultantAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
And they will be on two different lines if you simply comment out the group by. So, you also need to then use aggregate functions when using the group by


SELECT     contactdetails_Linkid, max(CASE WHEN contactdetails_typeid = 8 THEN contactdetails_value else ''end) AS Website,
                      max(CASE WHEN contactdetails_typeid = 7 THEN contactdetails_value else '' end) AS mobile
FROM         tbl_contactdetails
GROUP BY contactdetails_Linkid
0
 
Chris MichalczukConsultantAuthor Commented:
sorry spelling wrong as well in sample !!!

SELECT     contactdetails_id, CASE WHEN contactdetails_typeid = 8 THEN contactdetails_value as website, CASE WHEN contactdetails_typeid = 7 THEN contactdetails_value as mobile
FROM         tbl_contactdetails
GROUP BY contactdetails_id
0
 
mkobrinCommented:
When you say this won't work, are you getting errors with your syntax, or is the result nor what you are expecting?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Chris MichalczukConsultantAuthor Commented:
SELECT     contactdetails_id, CASE WHEN contactdetails_typeid = 8 THEN contactdetails_value AS Website,
                      CASE WHEN contactdetails_typeid = 7 THEN contactdetails_value AS mobile
FROM         tbl_contactdetails
GROUP BY contactdetails_id

error is INCORRECT SYNTAX near AS

if I remove AS Website get  INCORRECT SYNTAX near ','


heres a sample of the data

ContactDetails_ID      ContactDetails_LinkID      ContactDetails_LinkTypeID      ContactDetails_Value      ContactDetails_TypeID
60547      13234      1      07917154653              7
60548      15675      1      joe@blogs.com      8
                        


0
 
Chris MichalczukConsultantAuthor Commented:
SELECT     contactdetails_Linkid, CASE WHEN contactdetails_typeid = 8 THEN contactdetails_value AS Website,
                      CASE WHEN contactdetails_typeid = 7 THEN contactdetails_value AS mobile
FROM         tbl_contactdetails
GROUP BY contactdetails_Linkid
0
 
mkobrinCommented:
Try adding the END keyword before the AS, as I have shown here

SELECT     contactdetails_id, CASE WHEN contactdetails_typeid = 8 THEN contactdetails_value END AS Website,
                      CASE WHEN contactdetails_typeid = 7 THEN contactdetails_value END AS mobile
FROM         tbl_contactdetails
GROUP BY contactdetails_id
0
 
QlemoDeveloperCommented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0
All Courses

From novice to tech pro — start learning today.