Forum Stats

  • 3,851,709 Users
  • 2,264,014 Discussions
  • 7,904,821 Comments

Discussions

How to include another data to existing pivot?

2633101
2633101 Member Posts: 4
edited Nov 21, 2014 11:06AM in SQL & PL/SQL

Hi, I have this query:

select *

from (

        SELECT 'RD',

               '1001142302',

               defmac.COD_MACHIN,

               machine.NUM_SERIE,

               dmag.HARDWAR_ID,

               contadores.COD_CPTE,

               contadores.VAL_CPT_OL,

               defmac.PCT_REDIST,

               case when mensajes.COD_MSG_OL = 'MDC_DISCON' then '01'

                    when mensajes.COD_MSG_OL = 'SM_DISCON' then '02'

                    when mensajes.COD_MSG_OL = 'SM_NVRAM_ERROR' then '03'

                    when mensajes.COD_MSG_OL = '1397' then '04'

                    when mensajes.COD_MSG_OL = 'EPROM_CHANGE' then '05'

                    when mensajes.COD_MSG_OL = 'SM_POWER_OFF' then '06'

                    when mensajes.COD_MSG_OL = '1402' then '07'

                    when mensajes.COD_MSG_OL = '1405' then '07'

                    when mensajes.COD_MSG_OL = '1406' then '07'

                    when mensajes.COD_MSG_OL = '1408' then '07'

                    when mensajes.COD_MSG_OL = '1412' then '07'

                    when mensajes.COD_MSG_OL = '1413' then '07'

                    when mensajes.COD_MSG_OL = '1414' then '07'

                    when mensajes.COD_MSG_OL = '1415' then '07'

                    when mensajes.COD_MSG_OL = '1419' then '07'

                    when mensajes.COD_MSG_OL = '1431' then '07'

                    when mensajes.COD_MSG_OL = '1432' then '07'

                    when mensajes.COD_MSG_OL = 'WRAP_CI' then '07'

                    when mensajes.COD_MSG_OL = 'WRAP_CO' then '07'

                    when mensajes.COD_MSG_OL = 'WRAP_CTC' then '07'

                    when mensajes.COD_MSG_OL = 'WRAP_DOPPAY' then '07'

                    when mensajes.COD_MSG_OL = 'WRAP_GM' then '07'

                    when mensajes.COD_MSG_OL = 'WRAP_TI' then '07'

                    when mensajes.COD_MSG_OL = 'WRAP_TO' then '07'

                    when mensajes.COD_MSG_OL = '1399' then '08'

                    else '00'

                    end as "CODIGO"

        FROM site.sdefmac defmac

        inner join SITE.smachin machine on defmac.COD_MACHIN = machine.COD_MACHIN

        inner join SITE.SDEFMAG dmag on defmac.COD_MACHIN = dmag.COD_MACHIN

        inner join SITE.SLCOMPT contadores on defmac.COD_MACHIN = contadores.COD_MACHIN

        inner join SITE.SHISMSG mensajes on defmac.COD_MACHIN = mensajes.COD_OBJ

        where (defmac.DAT_EDEF IS NULL or defmac.COD_MEP = 1)

        AND defmac.COD_ETATSN = '0'

        AND dmag.cod_mep = '1'

        AND (contadores.dat_ses = '2013-09-15 00:00:00'

        AND (contadores.COD_CPTE in ('TI', 'TO', 'JP', 'HP', 'BI', 'GM')))

        AND mensajes.COD_MSG_OL in ('MDC_DISCON', 'SM_DISCON', '1397', 'EPROM_CHANGE', 'SM_NVRAM_ERROR', 'SM_POWER_OFF', '1402', '1405',

        '1406', '1408', '1412', '1413', '1414', '1415', '1419', '1431', '1432', 'WRAP_CI', 'WRAP_CO', 'WRAP_CTC', 'WRAP_DOPPAY',

        'WRAP_GM', 'WRAP_TI', 'WRAP_TO', '1399')

     )

     pivot

            (

             max(VAL_CPT_OL)

             for COD_CPTE in ('TI', 'TO', 'JP', 'HP', 'BI', 'GM')

            ) order by COD_MACHIN, CODIGO;

Here is a sample of the result of this query:

RD1001142302  COD_MACHINNUM_SERIEHARDWAR_IDPCT_REDISTCODIGOTITOJPHPBIGM
RD1001142302  AT020112231010013580256274100012332187220681467192206021570157426800379082
RD1001142302AT020112231010013580256274100082332187220681467192206021570157426800379082

But, what I want to do is to put the column CODIGO in this way

RD1001142302  COD_MACHINNUM_SERIEHARDWAR_IDPCT_REDISTTITOJPHPBIGMCOD1COD2
RD1001142302  AT02011223101001358025627410023321872206814671922060215701574268003790820108

Is this possible?

Thanks.

Message was edited by: 2633101 I solved the problem, Just include my query inside another select * from ( MY QUERY) PIVOT (); Thanks.

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,477 Red Diamond

    Hi,

    2633101 wrote:
    
    Hi, I have this query:
    
    select *
    from (
            SELECT 'RD',
                   '1001142302',
                   defmac.COD_MACHIN, 
                   machine.NUM_SERIE,
                   dmag.HARDWAR_ID, 
                   contadores.COD_CPTE,
                   contadores.VAL_CPT_OL, 
                   defmac.PCT_REDIST,
                   case when mensajes.COD_MSG_OL = 'MDC_DISCON' then '01'
                        when mensajes.COD_MSG_OL = 'SM_DISCON' then '02'
                        when mensajes.COD_MSG_OL = 'SM_NVRAM_ERROR' then '03'
                        when mensajes.COD_MSG_OL = '1397' then '04'
                        when mensajes.COD_MSG_OL = 'EPROM_CHANGE' then '05'
                        when mensajes.COD_MSG_OL = 'SM_POWER_OFF' then '06'
                        when mensajes.COD_MSG_OL = '1402' then '07'
                        when mensajes.COD_MSG_OL = '1405' then '07'
                        when mensajes.COD_MSG_OL = '1406' then '07'
                        when mensajes.COD_MSG_OL = '1408' then '07'
                        when mensajes.COD_MSG_OL = '1412' then '07'
                        when mensajes.COD_MSG_OL = '1413' then '07'
                        when mensajes.COD_MSG_OL = '1414' then '07'
                        when mensajes.COD_MSG_OL = '1415' then '07'
                        when mensajes.COD_MSG_OL = '1419' then '07'
                        when mensajes.COD_MSG_OL = '1431' then '07'
                        when mensajes.COD_MSG_OL = '1432' then '07'
                        when mensajes.COD_MSG_OL = 'WRAP_CI' then '07'
                        when mensajes.COD_MSG_OL = 'WRAP_CO' then '07'
                        when mensajes.COD_MSG_OL = 'WRAP_CTC' then '07'
                        when mensajes.COD_MSG_OL = 'WRAP_DOPPAY' then '07'
                        when mensajes.COD_MSG_OL = 'WRAP_GM' then '07'
                        when mensajes.COD_MSG_OL = 'WRAP_TI' then '07'
                        when mensajes.COD_MSG_OL = 'WRAP_TO' then '07'
                        when mensajes.COD_MSG_OL = '1399' then '08'
                        else '00'
                        end as "CODIGO"
            FROM site.sdefmac defmac
            inner join SITE.smachin machine on defmac.COD_MACHIN = machine.COD_MACHIN
            inner join SITE.SDEFMAG dmag on defmac.COD_MACHIN = dmag.COD_MACHIN
            inner join SITE.SLCOMPT contadores on defmac.COD_MACHIN = contadores.COD_MACHIN
            inner join SITE.SHISMSG mensajes on defmac.COD_MACHIN = mensajes.COD_OBJ
            where (defmac.DAT_EDEF IS NULL or defmac.COD_MEP = 1)
            AND defmac.COD_ETATSN = '0'
            AND dmag.cod_mep = '1'
            AND (contadores.dat_ses = '2013-09-15 00:00:00'
            AND (contadores.COD_CPTE in ('TI', 'TO', 'JP', 'HP', 'BI', 'GM')))
            AND mensajes.COD_MSG_OL in ('MDC_DISCON', 'SM_DISCON', '1397', 'EPROM_CHANGE', 'SM_NVRAM_ERROR', 'SM_POWER_OFF', '1402', '1405',
            '1406', '1408', '1412', '1413', '1414', '1415', '1419', '1431', '1432', 'WRAP_CI', 'WRAP_CO', 'WRAP_CTC', 'WRAP_DOPPAY',
            'WRAP_GM', 'WRAP_TI', 'WRAP_TO', '1399')
         )
         pivot
                (
                 max(VAL_CPT_OL)
                 for COD_CPTE in ('TI', 'TO', 'JP', 'HP', 'BI', 'GM')
                ) order by COD_MACHIN, CODIGO;
    
    Here is a sample of the result of this query:
    
    
    
    
    RD
    1001142302
      COD_MACHIN
    NUM_SERIE
    HARDWAR_ID
    PCT_REDIST
    CODIGO
    TI
    TO
    JP
    HP
    BI
    GM
    
    
    RD
    1001142302
      AT0201
    1223101001
    3580256274
    100
    01
    23321872
    20681467
    1922060
    2157015
    7426800
    379082
    
    
    RD
    1001142302
    AT0201
    1223101001
    3580256274
    100
    08
    23321872
    20681467
    1922060
    2157015
    7426800
    379082
    
    
    
    
    But, what I want to do is to put the column CODIGO in this way
    
    
    
    
    RD
    1001142302
      COD_MACHIN
    NUM_SERIE
    HARDWAR_ID
    PCT_REDIST
    TI
    TO
    JP
    HP
    BI
    GM
    COD1
    COD2
    
    
    RD
    1001142302
      AT0201
    1223101001
    3580256274
    100
    23321872
    20681467
    1922060
    2157015
    7426800
    379082
    01
    08
    
    
    
    
    Is this possible?
    
    Thanks.
    

    Sure, you can do that.  Exactly how depends on your data and your requirements.  See

    Right now, your output has a separate row for each distinct combination of 7 different columns, including codigo, but you want it to contain a separate row for each distinct combination of the first 6 columns, not including codigo.  What results do you want when the pivoted oclumns (ti, to, jp, hp, bi and gm) have different values for the different values of codigo, but the same values of the other 6 columns?

    Post some sample data and the exact results you want from that data.

    Simplify the problem.  Instead of doing an in-line view that joins 5 tables to produce 9 columns, post a question where you pretend to have 1 table that looks like the result set of that in-line view.  You don't need to incude all 9 columns, either: if you see how to solve the problem with, say, just 4 or 5 columns (including codigo, val_cpt_ol and cod_cpte), then you'll know how to it what you want for any number of columns.

This discussion has been closed.