Solved

Include column header in pivoted result

Posted on 2013-01-10
2
394 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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Suggested Solutions

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…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

762 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

12 Experts available now in Live!

Get 1:1 Help Now