Solved

Include column header in pivoted result

Posted on 2013-01-10
2
397 Views
Last Modified: 2013-01-10
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
0
Comment
Question by:g_currier
2 Comments
 
LVL 16

Accepted Solution

by:
Swadhin Ray earned 500 total points
ID: 38762385
Check this link:
http://www.folkstalk.com/2010/03/converting-columns-to-rows-in-oracle.html

As i do not have any environment so could not check.

Hope other experts can provide the query for you.
0
 

Author Closing Comment

by:g_currier
ID: 38762642
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!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now