Home » SQL & PL/SQL » SQL & PL/SQL » Display column values in the header
Display column values in the header [message #296932] Tue, 29 January 2008 09:15 Go to next message
tanyat
Messages: 3
Registered: January 2008
Location: PA
Junior Member
I need to write a query that does the following:

The tables:

Segment - lookup table, holds segment_id(PK), name
Segment_Attribute_Type - holds all the attribute
types, segment_attr_type_id(PK)
Segment_Attribute_Data - holds the attribute values
for the segments,
segment_id(FK),segment_attr_type_id(FK)

The segment doesn't have all the attr types assigned to it,
only those that apply will have values in Segment_Attribute_Data.
The query needs to display all the attr types in
Segment_Attribute_Type table as column names
and only those that have values for each segment will be populated as column values (one row per segment):

Segment_ID Segment_name Segment_Attr_Type1 Segment_Attr_Type2 Segment_Attr_Type3 .... 
---------- ------------ ------------------   ------------------ ------------------ --------
.....      .......             Attr Value1          Attr Value2        Attr Value3 .....


Is this possible to do with just SQL or PL/SQL or do I need a reporting tool for this?

TIA,
Tanya

[Updated on: Tue, 29 January 2008 10:15]

Report message to a moderator

Re: Display column values in the header [message #296948 is a reply to message #296932] Tue, 29 January 2008 10:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Could you post an example.

Quote:
Is this possible to do with just SQL or PL/SQL or do I need a reporting tool for this?

Yes and no.
In the end, headers are part of the tool that displays the returned data and not the returned data themselves.

See AskTom "Dynamic Report Headings" (where I say exactly the opposite).

Regards
Michel

[Updated on: Tue, 29 January 2008 10:41]

Report message to a moderator

Re: Display column values in the header [message #296951 is a reply to message #296948] Tue, 29 January 2008 11:02 Go to previous messageGo to next message
tanyat
Messages: 3
Registered: January 2008
Location: PA
Junior Member
Here is an example of what I need:

SELECT segment_id, segment_name 
FROM segment 
WHERE segment_id=1041;

SQL> /

SEGMENT_ID SEGMENT_NAME
---------- ----------------------------------------
      1041 RM - ValueClick [RSP]


--

SELECT segment_attribute_type_id Attr_Type_id,
       display_text Name
FROM segment_attribute_type
ORDER BY segment_attribute_type_id;

ATTR_TYPE_ID NAME
------------ -----------------------------------
          -8 A
          -7 AA
          -6 AAA
          -5 B
          -4 BB
          -3 BBB
          -2 C
          -1 CC
           1 CCC
           2 D
           3 DD
           4 DDD
           5 X

--

SELECT segment_attribute_data_id Attr_Data_ID,
       segment_attribute_type_id Attr_Type_ID,
       segment_attribute_value Attr_Value
FROM segment_attribute_data
WHERE segment_id=1041

SQL> /

ATTR_DATA_ID ATTR_TYPE_ID ATTR_VALUE
------------ ------------ --------------------
        6724           -8 2
        1305           -3 IND
        1306           -2 Y
        2702            4 Y
        5285            5 Y

--
Need to display:

Segment     A       AA      AAA       B      BB      BBB      C  .....
------- ----- -------- -------- ------- ------- -------- ------ ------ 
1041        2                                        IND      Y  .....
Re: Display column values in the header [message #296954 is a reply to message #296951] Tue, 29 January 2008 11:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read the link (or rather the page pointed by the link).

Regards
Michel

[Updated on: Tue, 29 January 2008 11:42]

Report message to a moderator

Re: Display column values in the header [message #296960 is a reply to message #296932] Tue, 29 January 2008 12:05 Go to previous message
tanyat
Messages: 3
Registered: January 2008
Location: PA
Junior Member
Thanks, Michel - this is exactly what I need.

Best regards,
Tanya
Previous Topic: Materilized View Refresh
Next Topic: Is this a good way of using table function?
Goto Forum:
  


Current Time: Mon Dec 05 18:50:41 CST 2016

Total time taken to generate the page: 0.09190 seconds