Solved

pivot table sql 2000 case when statement

Posted on 2010-11-11
8
377 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
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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 69

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query Syntax Join 4 39
SQL Query 26 64
SQL Server - Select Distinct and Count Rows for Each Distinct Row Returned 2 20
Query Help - MSSQL - Averages 5 27
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
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…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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