CODEWITHSUNDEEP
sqlpivotsybase
user726720
user726720

Reputation: 1247

Query distinct row value and separate into different columns

I have a table which has the following content:

Customer ID             Customer type                
---------               --------
123                        A
123                        alpha
123                        Beta   
456                        B
456                        BGamma
456                        BBeta

I want to achieve the following:

Customer_ID    Customer_type     Customer_E1    Customer E_2   Customer E_3 
---------      -----------       -----------    -------------  -------------
123                 A               Alpha          Beta
456                 B               BGamma         BBeta

Can you please help me with a Sybase query to achieve this?

Upvotes: 0

Views: 1828

Answers (2)

Taryn
Taryn

Reputation: 247850

I don't believe Sybase has a PIVOT function which is what you are trying to do. So you might be able to implement something like this which uses an aggregate function with a CASE statement:

select customerid,
  max(case when rn = 1 then customertype else null end) CustomerType,
  max(case when rn = 2 then customertype else null end) Customer_E1,
  max(case when rn = 3 then customertype else null end) Customer_E2
from
(
  select CustomerID, CustomerType,
    row_number() over(partition by CustomerID order by CustomerID) rn
  from yourtable
) src
group by customerid

See SQL Fiddle with Demo

Note: I see a potential issue with the first column of data CustomerType. My suggestion would be to have this in a separate column from the other values. Then this will not take part in the row_number() assignment and it will be easier to guarantee that the correct value will appear.

Upvotes: 1

DWright
DWright

Reputation: 9500

Let me suggest that you normalize your data model. Clearly 'A' vs 'Alpha' are different kinds of data values (they describe different things), but you have them in the same field, which is causing you all sorts of problems. Take a look at this article on database normalization, which has a good discussion of the concepts involved.

Basically, what you want to end up with is having alpha and beta type values in a separate table that you can key into via Customer ID and Customer Type.

Upvotes: 0

Related Questions