Solved

How do I convert columns to rows in Sybase

Posted on 2011-09-21
7
3,219 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
  • 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: 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.

 

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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 was originally published on Monitis Blog, you can check it  here .   Some years back, I worked as the CTO.  During my tenure, I had a head of IT support reporting to me.  He did his job quite well and had a commendable sense of duty…
Building a cohesive image for your brand is vital to making an impression on consumers. When the economy is tough, brands do better than unbranded  products. This can have a huge impact on your long-term profits, as the economy goes up and down.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

733 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