How do I convert columns to rows in Sybase

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
Who is Participating?
Joe WoodhouseConnect With a Mentor Principal ConsultantCommented:
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.
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.

  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.
2shr_dbAuthor Commented:
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.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

2shr_dbAuthor Commented:
thanks Joe, this is much better than a cursor.
Good solution.  Much better than mine!
Joe WoodhousePrincipal ConsultantCommented:
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. :)
2shr_dbAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.