Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I convert columns to rows in Sybase

Posted on 2011-09-21
7
Medium Priority
?
3,713 Views
Last Modified: 2012-05-12
How to I convert columns to rows in Sybase. In effect i want to utilize the transpose function of excel.

Input Table
Case  exam_id  cpt_Seq  cpt_code  
AA           1                1             20
AA           1                2             30
AA           1                3             40
AA           2                1             50
AA           2                2             60
BB           1                 1             70
BB           1                2             80
BB           2                1             90
BB           2                2             100

where case, exam_id are the key
cpt_seq is the sequence for cpt_code for each values of case, exam_id

Output Table
Case    exam_id   cpt_code1  cpt_code2   cpt_code3    cpt_code4
AA                 1                20               30                  40
AA                 2                50               60                  
BB                 1                70               80
BB                 2                90              100
0
Comment
Question by:2shr_db
[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
  • 3
  • 2
  • 2
7 Comments
 
LVL 2

Expert Comment

by:jan24
ID: 36576976
I don't have Sybase, but I understand that it is fundamentally SQL.  If that's right, then the query below should be a starting point.  I've not been able to test it, so it might need a bit of debugging.  It assumes that the pairs (Case,exam_id) for a unique identifier, i.e. there are no duplicate sets of records.

SELECT
  it.Case,it.exam_id,
  it1.cpt_code AS cpt_code1,
  it2.cpt_code AS cpt_code2,
  it3.cpt_code AS cpt_code3,
  it4.cpt_code AS cpt_code4
FROM (SELECT Case,exam_id FROM Input_Table GROUP BY Case,exam_id) it
LEFT OUTER JOIN Input_Table it1 ON it.Case=it1.Case AND it.exam_id=it1.exam_id AND it1.cpt_Seq=1
LEFT OUTER JOIN Input_Table it2 ON it.Case=it2.Case AND it.exam_id=it2.exam_id AND it2.cpt_Seq=2
LEFT OUTER JOIN Input_Table it3 ON it.Case=it3.Case AND it.exam_id=it3.exam_id AND it3.cpt_Seq=3
LEFT OUTER JOIN Input_Table it4 ON it.Case=it4.Case AND it.exam_id=it4.exam_id AND it4.cpt_Seq=4

I'm sure there are more elegant solutions, but hopefully the above gives you a starting point for something that will do the job.  If cpt_Seq can go higher than 4 then you would need to expand the query in the relevant places.
0
 

Author Comment

by:2shr_db
ID: 36581718
Thanks Jan, I knew I would accomplish it with self join but I was looking for a better way of doing it.
Thanks Anyways.  :-)

Looks like that's the route I have to take.
0
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 2000 total points
ID: 36595004
You can do this without a self-join:

select      Case
,            exam_id
,            cpt_code1 = max(case when cpt_seq=1 then cpt_code else null end)
,            cpt_code2 = max(case when cpt_seq=2 then cpt_code else null end)
,            cpt_code3 = max(case when cpt_seq=3 then cpt_code else null end)
,            cpt_code4 = max(case when cpt_seq=4 then cpt_code else null end)
from            Input_Table
group by      Case
,            exam_id


Look at the expressions from the inside out. Here we use a CASE to say (looking at the first one) return a NULL except when cpt_seq=1... in which case return the value of cpt_code.

We ask for the MAX of all of that, with a GROUP BY Case and exam_id, because any value will always sort higher than a NULL.

Depending on the client you use to run this, you might see literal NULL values. If so, we can suppress them by wrapping the expression in an ISNULL function:

            cpt_code1 = isnull(max(case when cpt_seq=1 then cpt_code else null end), "")

this will instead now return an empty string when there was nothing to display, which should give the exact output you're looking for.

Note you have to hardcore a column for each possible value of cpt_seq. If you want a solution that can handle an arbitrary number of these, then it's probably best to load them into a temp table and join to that. Let us know if you need that solution instead.
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

by:2shr_db
ID: 36600385
thanks Joe, this is much better than a cursor.
0
 
LVL 2

Expert Comment

by:jan24
ID: 36600429
Good solution.  Much better than mine!
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 36635362
Note the limits of this approach - you must hardcode an expression for each value you want as a column, and it also assumes there is only ever one value for each unique combination. (It's fine if there's more than one instance of that value, but there can only be one distinct value.)

And yes, this is a good example of why I say cursors are just about never required. :)
0
 

Author Comment

by:2shr_db
ID: 36681080
Yeah, the reason Input up this question is coz I didn't want to use cursors. I just wanted a way,  I can customize your approach into my code. Thanks again.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
On September 18, Experts Exchange launched the first installment of the Help Bell, a new feature for Premium Members, Team Accounts, and Qualified Experts. The Help Bell will serve as an additional tool to help teams increase question visibility.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

721 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