Table1 has those values:
ITEM_TYPE ITEM_KEY NAME TEXT_VALUE
WF_TX11 MD1000543 CHANNEL_NAME CHANNEL1
WF_TX11 MD1000543 COMPLIANCE_NAME COMPLIANCE1
WF_TX11 MD1000543 DRI_SYSTEMS ME
WF_TX11 MD1000543 TX_DATE 10 Sep 2007
WF_TX11 MD1000601 CHANNEL_NAME CHANNEL2
WF_TX11 MD1000601 COMPLIANCE_NAME COMPLIANCE2
WF_TX11 MD1000601 DRI_SYSTEMS YOU
WF_TX11 MD1000601 TX_DATE 11 Sep 2007
This function below would return any channel in the given period:
SELECT DISTINCT Package.Getitemattrtext('WF_TX11',
Item_Key,
'CHANNEL_NAME') CHANNEL_NAME
FROM Table1
WHERE NAME = 'TX_DATE'
AND To_Date(Text_Value) >= '01/sep/07'
AND To_Date(Text_Value) <= '30/sep/07'
Result:
CHANNEL_NAME
CHANNEL1
CHANNEL2
If I use the same function for DRI_SYSTEMS:
SELECT DISTINCT Package.Getitemattrtext('WF_TX11',
Item_Key,
‘DRI_SYSTEMS‘) ‘DRI_SYSTEMS’ FROM Table1
WHERE NAME = 'TX_DATE'
AND To_Date(Text_Value) >= '01/sep/07'
AND To_Date(Text_Value) <= '30/sep/07'
Result:
DRI_SYSTEMS
ME
YOU
How can I extract this information in an SQL so that I get something like the below?
CHANNEL_NAME TX_DATE COMPLIANCE_NAME DRI_SYSTEMS
CHANNEL1 10 Sep 2007 COMPLIANCE1 ME
CHANNEL2 11 Sep 2007 COMPLIANCE2 YOU
Thanks