Solved

Include column header in pivoted result

Posted on 2013-01-10
2
399 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

803 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