Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

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




0
Chris Michalczuk
Asked:
Chris Michalczuk
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
Mark WillsTopic 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
 
QlemoC++ DeveloperCommented:
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now