Solved

pivot table sql 2000 case when statement

Posted on 2010-11-11
8
382 Views
Last Modified: 2012-06-22
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
Comment
Question by:Chris Michalczuk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 

Author Comment

by:Chris Michalczuk
ID: 34118168
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
 
LVL 7

Expert Comment

by:mkobrin
ID: 34118189
When you say this won't work, are you getting errors with your syntax, or is the result nor what you are expecting?
0
 

Author Comment

by:Chris Michalczuk
ID: 34118317
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
How To Reduce Deployment Times With Pre-Baked AMIs

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

 

Author Comment

by:Chris Michalczuk
ID: 34118321
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
 
LVL 7

Expert Comment

by:mkobrin
ID: 34118383
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
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 34119449
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
 
LVL 70

Expert Comment

by:Qlemo
ID: 34387621
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

CHALLENGE LAB: Troubleshooting Connectivity Issues

Goal: Fix the connectivity issue in the lab's AWS environment so that you can SSH into the provided EC2 instance.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question