Solved

How do I convert columns to rows in Sybase

Posted on 2011-09-21
7
3,480 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
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

This article describes how to import Lotus Notes Contacts into Outlook 2016, 2013, 2010 and 2007 etc. with a few manual steps. You can easily export and migrate Lotus Notes contacts into Microsoft Outlook without having to use any third party tools.
This article is written by John Gates, CISSP. Gates, the SNUG President-Elect, currently holds the position of Manager of Information Systems at Lake Park High School in Roselle, Illinois.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

617 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