10 Replies Latest reply: Apr 24, 2011 1:22 PM by 857372 RSS

    SQL to display row data in columns

    602882
      I have a table with a column for a mnemonic, its value, and time the change occured. the mnemonics change over different periods of time. I had a request from a user to display all the mnemonics in columns with the time listed for each row. For times where there is no value for the mnemonic, it would either be blank or have the last times value.

      so here would be my sample mnemonic table:

      PK_ID, Mnemonic, Value, Time in Seconds
      1, MA, 100, 10000
      2, MA, 102, 10001
      3, MB, 100, 10001
      4, MC, 200, 10001
      5, MB, 220, 10003
      6, MA, 103, 10004

      I need to format it like this:

      Time, MA, MB, MC
      10000, 100,___,___
      10001, 102, 100, 200
      10002,___,____,____
      10003,____,____, 220
      10004, 103,___,___

      I tried doing a case statement in the select on the Mnemonic and I got the columns but my result is only one value per line and the time repeating. My output looks like this:

      Time, MA, MB, MC
      10000, 100,____,____
      10001, 102,____,____
      10001, ___,100,____
      10001, ___,___, 200
      10003, ____, 220,____
      10004, 103,____,____

      here is a little bit of my code. my example above is simplified. there are a few joins involved to get some other needed data for the report. any help is much appreciated! I know this can be done fairly simply. I'm just stuck!

      SELECT
      hsv.hs_value_id,
      hsv.hs_telem_time,
      f.file_name,
      CASE WHEN t.mnemonic = 'ZPALN1BT' THEN TO_CHAR(hsv.change_value) ELSE '0' END AS "ZPALN1BT",
      CASE
      WHEN t.mnemonic = 'ZPAARDTM' THEN TO_CHAR(hsv.change_value)
      ELSE '0'
      END AS "ZPAARDTM"
      ,
      CASE
      WHEN t.mnemonic = 'SSCSSN16' THEN TO_CHAR(hsv.change_value)
      ELSE '0'
      END AS "SSCSSN16"
      ,
      CASE
      WHEN t.mnemonic = 'SSPSP024' THEN TO_CHAR(hsv.change_value)
      ELSE '0'
      END AS "SSPSP024"
      ,
      CASE
      WHEN t.mnemonic = 'SMNCMAXT' THEN TO_CHAR(hsv.change_value)
      ELSE '0'
      END AS "SMNCMAXT"
      ,
      CASE
      WHEN t.mnemonic = 'SSWORD2' THEN TO_CHAR(hsv.change_value)
      ELSE '0'
      END AS "SSWORD2"
      ,
      CASE
      WHEN t.mnemonic = 'SMDCMAXT' THEN TO_CHAR(hsv.change_value)
      ELSE '0'
      END AS "SMDCMAXT"
      ,
      CASE
      WHEN t.mnemonic = 'SDPLSTPM' THEN TO_CHAR(hsv.change_value)
      ELSE '0'
      END AS "SDPLSTPM"
      ,
      CASE
      WHEN t.mnemonic = 'SSPSP144' THEN TO_CHAR(hsv.change_value)
      ELSE '0'
      END AS "SSPSP144"
      from hs_value_tbl hsv, tlm_format_tbl t, file_tbl f, hs_file_tbl hsf
      where hsv.pk_id = t.pk_id
      and hsv.hs_file_id = hsf.hs_file_id
      and f.file_id = hsf.file_id
      and hsv.hs_telem_time = 938866052096
      order by hsv.hs_value_id;

      Message was edited by:
      JfromLA
        • 1. Re: SQL to display row data in columns
          Tubby
          Well, you've got 2 concepts here. One, pivot the results and two, make up rows for missing data.

          So, i've named my table appropriately for this query :D)

          This works ONLY if you know the domain for Mnemonic (column2 in my table). boundaries and blank_filler are being used to construct the rows that may be missing for 'time in seconds' which is column4 in my table.
          create table this_will_be_ugly (column1 number, column2 varchar2(2), column3 number, column4 number);

          insert into this_will_be_ugly values (1, 'MA', 100, 10000);
          insert into this_will_be_ugly values (2, 'MA', 102, 10001);
          insert into this_will_be_ugly values (3, 'MB', 100, 10001);
          insert into this_will_be_ugly values (4, 'MC', 200, 10001);
          insert into this_will_be_ugly values (5, 'MB', 220, 10003);
          insert into this_will_be_ugly values (6, 'MA', 103, 10004);
          COMMIT;

          ME_XE?with
            2     boundaries as
            3  (
            4     select
            5        min(column4) as min_column4,
            6        max(column4) as max_column4
            7     from this_will_be_ugly
            8  ),
            9     blank_filler as
          10  (
          11     select min_column4 + level - 1 AS to_fill_da_gapz
          12     from boundaries
          13     connect by level < max_column4 - min_column4 + 2
          14  ),
          15     non_aggregrate_data as
          16  (
          17     select
          18        t.column1,
          19        t.column2,
          20        t.column3,
          21        b.to_fill_da_gapz AS column4
          22     from this_will_be_ugly t, blank_filler b
          23     where b.to_fill_da_gapz = t.column4 (+)
          24  )
          25  select
          26     column4,
          27     max(decode(column2, 'MA', column3, NULL)) AS MA,
          28     max(decode(column2, 'MB', column3, NULL)) AS MB,
          29     max(decode(column2, 'MC', column3, NULL)) AS MC
          30  from non_aggregrate_data
          31  group by column4
          32  order by column4 asc
          33  /

                     COLUMN4                 MA                 MB                 MC
          ------------------ ------------------ ------------------ ------------------
                       10000                100
                       10001                102                100                200
                       10002
                       10003                                   220
                       10004                103

          5 rows selected.

          Elapsed: 00:00:00.68
          ME_XE?
          • 2. Re: SQL to display row data in columns
            602882
            Thanks so much and the table name you chose is appropriate for me although obviously not so much for you. I got it to work with my table but is there a way to place that last value instead of an empty one? I actually have all the mnemonic's set to an initial value at the first time stamp so the first row in this query would always have data. thanks again!
            • 3. Re: SQL to display row data in columns
              635362
              Hi Tubby,
              Thank you for your query,

              but my scenario is liitle bit different.

              Here column2 in your table is dynamic in my scenario.

              can you please help in query to get the same kind of data which are dynamic values in column2 and with out hard coding the values like 'MA','MB','MC'.

              Thanks in Advance.

              Krish
              • 4. Re: SQL to display row data in columns
                Sezhiyan
                Hi,

                Can you use the WM_CONCAT function.... it will convert the rows into columns ..

                Regards
                • 5. Re: SQL to display row data in columns
                  848583
                  HI please see the below example,


                  original
                  --------
                  SELECT * FROM STAGE;
                  CU PR OR OPERATOR
                  -- -- -- ---------
                  C1 P1 D1 OP1
                  C1 P2 D2 OP2
                  C1 P3 D3 OP3
                  C1 P1 D4 OP3

                  converted to rows
                  ------------------
                  SELECT customer,
                  MAX(LTRIM( sys_connect_by_path( product, ',' ) , ',')) prod_str,
                  MAX(LTRIM( sys_connect_by_path( order_date , ',' ) , ',')) order_str,
                  MAX(LTRIM( sys_connect_by_path( OPERATOR , ',' ) , ',')) OPERATOR_str
                  FROM
                  (
                  SELECT customer, product , order_date , OPERATOR,
                  row_number() over (PARTITION BY customer ORDER BY ROWID) rn
                  FROM STAGE
                  )
                  START WITH rn = 1
                  CONNECT BY customer = PRIOR customer AND PRIOR rn = rn -1
                  GROUP BY customer;


                  Source: http://wiki.oracle.com/thread/1084696/Select+ROWs+in+one+column


                  Also please view the below link .

                  rows to column for huge number of records
                  rows to column for huge number of records


                  Thanks,

                  Balaji K.
                  • 6. Re: SQL to display row data in columns
                    635362
                    Thanks Balaji for your reply,

                    I believe that, it works to display the all records of a column in rows row wise,
                    But here my scenario is like, i dont know the list of the records, it is dynamical.
                    • 7. Re: SQL to display row data in columns
                      BluShadow
                      Sezhiyan wrote:
                      Hi,

                      Can you use the WM_CONCAT function.... it will convert the rows into columns ..

                      Regards
                      No it doesn't.

                      WM_CONCAT causes data to be aggregated into a single string and thus a single column. It does not create multiple columns.
                      It is also an undocumented function and the behaviour of it is not defined and may change in future versions of Oracle, so it should not be used in a production database as you will not get Oracle support for any code that uses it.
                      • 8. Re: SQL to display row data in columns
                        635362
                        Hi Tubby,
                        Thanks, your query helped me a lot to resolve my issue.

                        But still im strucking up with the table data.
                        In my scenario the table data is little bit different.
                        The column4 data will not be in order. Please see the below code
                        {
                        create table this_will_be_ugly (column1 number, column2 varchar2(2), column3 number, column4 number);

                        insert into this_will_be_ugly values (1, 'MA', 100, 12367);
                        insert into this_will_be_ugly values (2, 'MA', 102, 33323);
                        insert into this_will_be_ugly values (3, 'MB', 100, 33323);
                        insert into this_will_be_ugly values (4, 'MC', 200, 33323);
                        insert into this_will_be_ugly values (5, 'MB', 220, 43555);
                        insert into this_will_be_ugly values (6, 'MA', 103, 76765);
                        COMMIT;

                        I need to display the data like below.

                        COLUMN4 MA MB MC
                        --------------------------------------------------------------------------------
                        --------------------------------------------------------------------------------------------------
                        ------------------
                        12367 100
                        33323 102 100 200
                        43555 220
                        76765 103


                        }

                        Please help me to resolve.
                        Thanks in Advance
                        Krish
                        • 9. Re: SQL to display row data in columns
                          Manjusha Muraleedas
                          check this query

                          WITH Mnemonic_tab AS (SELECT 'MA' Mnemonic, 100 VALUE, 10000 "TIME IN Seconds" FROM dual UNION ALL
                                                   SELECT 'MA' Mnemonic, 102 VALUE, 10001 "TIME IN Seconds" FROM dual UNION ALL
                                                   SELECT 'MB' Mnemonic, 100 VALUE, 10001 "TIME IN Seconds" FROM dual UNION ALL
                                                   SELECT 'MC' Mnemonic, 200 VALUE, 10001 "TIME IN Seconds" FROM dual UNION ALL
                                                   SELECT 'MB' Mnemonic, 220 VALUE, 10003 "TIME IN Seconds" FROM dual UNION ALL
                                                   SELECT 'MA' Mnemonic, 103 VALUE, 10004 "TIME IN Seconds" FROM dual )
                          SELECT * FROM Mnemonic_tab PIVOT(MAX(VALUE) FOR Mnemonic IN ('MA','MB','MC'))
                          • 10. Re: SQL to display row data in columns
                            857372
                            Thanks for the reply Manjusha.
                            but my scenario is not like limited records. It may thousands of records. so i can not write that kind of query.
                            Can u plz give any other idea.
                            ..AR