Solved

How do I convert columns to rows in Sybase

Posted on 2011-09-21
7
3,401 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 500 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 

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: 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.

Question has a verified solution.

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

This article will help to fix the below error for MS Exchange server 2010 I. Out Of office not working II. Certificate error "name on the security certificate is invalid or does not match the name of the site" III. Make Internal URLs and External…
Here's how to start interacting with our community through Post.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

729 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