asked on

Include column header in pivoted result

Hi,

I do not consider myself a novice but once in a while, a problem comes along that may be simple to someone else but for me is incomprehensible.

I hope someone can help shed some light on this.

I would like to include the column header in the result of a query.

If you have ever used PL/SQL developer from AllroundAutomations you will know what I am talking about when I say I want my result to look like the pivoted record view. I have tried to figure out how this done by digging into the program itself but I could not find how to do it in SQL or PL/SQL (though I am sure there is a way)

Here is what I want to do:

take this format:

table 1
COLUMN_A	COLUMN_B	COLUMN_C	COLUMN_D
----------	----------	----------	----------
Val_A1		Val_B1		Val_C1		Val_D1
Val_A2		Val_B2		Val_C2		Val_D2


and transform it to this:

table 2
COLUMN_NAME	COLUMN_VALUES		
-----------	-------------	
COLUMN_A	Val_A1
COLUMN_B	Val_B1
COLUMN_C	Val_C1
COLUMN_D	Val_D1

Open in new window

Can someone help? I have been at this for 2 days now and am no closer to a solution.

The reason I need this is so that I can join the column header value to another table and include the values of each based on the header. The result set would be fed into another table and loaded into a migration tool. I would just change the structure of the tables to do this more easily, but that is not an option available to me.

I appreciate any advice or help.

Thanks

ASKER CERTIFIED SOLUTION

Swadhin Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

g_currier

ASKER

Thanks, that was what I needed to get me close enough to the solution I am looking for.

The query (modified to fit my data) and the output:

SQL Statement which produced this data:
  SELECT DISTINCT CASE to_char(PIVOT)
                           WHEN '1' THEN to_char(sach_id)
                           WHEN '2' THEN PHYSICAL_CHARACTERISTIC
                           WHEN '3' THEN ORIGIN
                           WHEN '4' THEN INTERNAL_NAME
                           WHEN '5' THEN CHEMICAL_NAME
                           WHEN '6' THEN SHIPPING_NAME
                           WHEN '7' THEN CAS_NR_1
                           WHEN '8' THEN CAS_NR_2
                           WHEN '9' THEN CAS_NR_3
                           WHEN '10' THEN WGK
                           WHEN '11' THEN to_char(UN_NR)
                           WHEN '12' THEN ADR
                           WHEN '13' THEN ADR_PACKING_GROUP
                           WHEN '14' THEN IMDG
                           WHEN '15' THEN IMDG_PACKING_GROUP
                           WHEN '16' THEN MARINE_POLL
                           WHEN '17' THEN IATA
                           WHEN '18' THEN IATA_PACKING_GROUP
                           WHEN '19' THEN RVALUE
                           WHEN '20' THEN SVALUE
                           WHEN '21' THEN to_char(VAPOR_MMHG)
                           WHEN '22' THEN TOXICITY_MEMO
                           WHEN '23' THEN REGION
                           WHEN '24' THEN to_char(VERSION)
                           WHEN '25' THEN COMPLETED
                           ELSE NULL
                       END VAL
                      ,PIVOT list_order
    FROM MV_IHS_EUCLP_NO_ANX1
          ,(SELECT ROWNUM PIVOT 
            FROM dual
           CONNECT BY LEVEL <= 25)
   WHERE sach_id = 54070
   ORDER BY PIVOT;

VAL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  LIST_ORDER                             
54070                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                1                                      
LIQUID                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               2                                      
NATURAL PROD. LIQUID                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 3                                      
CAMPHOR OIL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          4                                      
Camphor oil (Cinnamomum camphora)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    5                                      
Camphor Oil                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          6                                      
8008-51-3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            7                                      
92201-50-8                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           8                                      
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     9                                      
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    10                                     
1130                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 11                                     
3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    12                                     
III                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  13                                     
3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    14                                     
III                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  15                                     
P                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    16                                     
3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    17                                     
III                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  18                                     
R 10-38-43-50/53-65                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  19                                     
S 24-37-61-62                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        20                                     
1.51904                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              21                                     
ULD 4.4.06: Xn R 65 was introduced due to HC- content of 30% (+ MSDS Nitsche)                                                                                                                                                                                                                                                                                                                                                                                                                                        22                                     
IFRA                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 23                                     
99                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   24                                     
YES                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  25

Open in new window

Appreciate the link!