CODEWITHSUNDEEP
sqloracle-databasepivotsybase
smv
smv

Reputation: 485

Need to create pivot script in sybase

I have a table like 

ID     ENVI              SERVER               GROUP         ACTIVE
==     ====              ======              ======         ======
1      Developent        AREGION_1            A               1
2      Developent        AREGION_2            A               1
3      Developent        AREGION_3            A               1
4      Developent        BREGION_1            B               1
5      Developent        BREGION_2            B               1
6      Developent        BREGION_3            B               1
7      Developent        CREGION_1            C               1
8      Developent        CREGION_3            C               1
9      Developent        A1REGION             A               1
10     Developent        A2REGION             A               1
11     Developent        ABCREGION            A               1

i need to write a sp with input Parameter ENVI that will return the result like below

ENVI                A             B              C
====                =========     =========      =========
Development         AREGION_1     BREGION_1      CREGION_1  (All servers Ending with _1)
Development         AREGION_2     BREGION_2                 (All servers Ending with _2)
Development         AREGION_3     BREGION_3      CREGION_3  (All servers Ending with _3)
Development         A1REGION     
Development         A2REGION     
Development         ABCREGION

the conditions Are All the servers ending with _ number should come in first sorted order. If any one column do not have value for the row that field should e null or empty. Any server with random name under any group must be placed last under that group.

Please help me creating the sp

thanks in advance

Upvotes: 2

Views: 5362

Answers (2)

Taryn
Taryn

Reputation: 247850

You did not specify what version of sybase you are using, this answer assumes you have a version with access to windowing functions. Sybase does not have a PIVOT function so you will have to replicate it using an aggregate function with a CASE expression.

The following code should get the result that you want:

select envi,
  max(case when serverGroup = 'A' then server end) as A,
  max(case when serverGroup = 'B' then server end) as B,
  max(case when serverGroup = 'C' then server end) as C
from
(
  select envi,
    server,
    serverGroup,
    case 
      when frn > rn then frn
      else rn
    end rn    
  from
  (
    select envi,
      server, 
      serverGroup,
      case 
        when charindex('_', SERVER) = 0
        then 0
        else substring(SERVER, charindex('_', SERVER)+1, len(SERVER))
      end frn, 
      row_number() over(partition by envi, serverGroup 
                        order by substring(SERVER, charindex('_', SERVER), len(SERVER)+1)) rn
    from ytable
  ) d
) x
group by envi, rn
order by rn;

See SQL Fiddle with Demo. note: the demo is on SQL Server.

This gives the result:

|       ENVI |         A |         B |         C |
--------------------------------------------------
| Developent | AREGION_1 | BREGION_1 | CREGION_1 |
| Developent | AREGION_2 | BREGION_2 |    (null) |
| Developent | AREGION_3 | BREGION_3 | CREGION_3 |
| Developent |  A1REGION |    (null) |    (null) |
| Developent |  A2REGION |    (null) |    (null) |
| Developent | ABCREGION |    (null) |    (null) |

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270663

This is an aggregation query, although your final result does not include one of the aggregation columns (the modified server name).

select envi,
       max(case when group = 'A' then server end) as A,
       max(case when group = 'B' then server end) as B,
       max(case when group = 'C' then server end) as C
from t
group by envi,
         (case when server like '%[_]%' and server not like '%[_]%[^0-9]%'
               then left(server, charindex('_', server) - 1)
               else server
          end)

The like logic looks for a server name that has an underscore and only has numbers after the underscore.

Upvotes: 0

Related Questions