CODEWITHSUNDEEP
sqlcastingpivotcasesybase
Hpjchobbes
Hpjchobbes

Reputation: 1319

Sybase SQL CASE with CAST

I have a Sybase table (which I can't alter) that I am trying to get into a specific table format. The table contains three columns all which are string values, with an id (which is not unique), a "position" which is a number that represents a field name, and a field column that is the value. The table looks like:

id     position     field
100    0            John
100    1            Jane
100    2            25
100    3            50
101    0            Dave
101    3            30

Position 0 means "SalesRep1", Position 1 means "SR1Commission", Position 2 means "SalesRep2", and Position 3 means "SR2Commission". I am trying to get a table that looks like following, with the Commission columns being decimals instead of strings:

id     SalesRep1  SR1Commission    SalesRep2    SR2Commisson
100    John       25               Jane         50
101    Dave       30               NULL         NULL

I've gotten close using CASE, but I end up with only one value per row and not sure there's a way to do what I want. I also have problems with trying to get CAST included to change the commission values from strings to decimals. Here's what I have so far:

SELECT id
CASE "position" WHEN '0' THEN field END AS SalesRep1,
CASE "position" WHEN '1' THEN field END AS SalesRep2,
CASE "position" WHEN '2' THEN field END AS SR1Commission,
CASE "position" WHEN '3' THEN field END AS SR2Commission
FROM v_custom_field WHERE id = ?

This gives me the following result when querying for id 100:

id    SalesRep1  SR1Commission  SalesRep2  SR2Commission
100   John       NULL           NULL       NULL
100   NULL       25             NULL       NULL
100   NULL       NULL           Jane       NULL
100   NULL       NULL           NULL       50

This is close, but I want to 'collapse' the rows down into one row based off of the id as well as cast the commission values to numbers. I tried adding in a CAST(field AS DECIMAL) I'm not sure if this is even the right direction to go, and was looking into PIVOT, but Sybase doesn't seem to support that.

Upvotes: 0

Views: 456

Answers (1)

Bacon Bits
Bacon Bits

Reputation: 32220

This is known as an entity-attribute-value table. They're a pain to work with because they're one step removed from being relational data, but they're very common for user-defined fields in applications.

If you can't use PIVOT, you'll need to do something like this:

SELECT DISTINCT s.id,
    f0.field AS SalesRep1,
    CAST(f1.field AS DECIMAL(20,5)) AS SR1Commission,
    f2.field AS SalesRep2,
    CAST(f3.field AS DECIMAL(20,5)) AS SR2Commission
FROM UnnamedSalesTable s
LEFT JOIN UnnamedSalesTable f0
    ON  f0.id = s.id AND f0.position = 0
LEFT JOIN UnnamedSalesTable f1
    ON  f1.id = s.id AND f1.position = 1
LEFT JOIN UnnamedSalesTable f2
    ON  f2.id = s.id AND f2.position = 2
LEFT JOIN UnnamedSalesTable f3
    ON  f3.id = s.id AND f3.position = 3

It's not very fast because it's a ton of self-joins followed by a DISTINCT, but it does work.

Upvotes: 1

Related Questions