BI Applications: Privileges within a PLSQL procedure

BI Applications: Privileges within a PLSQL procedure: "Oracle expects that we mention explicitly what kind of privileges the PLSQL procedure has. If we do not specify it properly there are chance..."

BI Applications: Accessing Table Names of other Schemas

BI Applications: Accessing Table Names of other Schemas: "Many times we would want to select the table names from another schema. In such cases below SELECT would help.. select TABLE_NAME FROM ALL_..."

BI Applications: Database Character Set of a DB

BI Applications: Database Character Set of a DB: "This query will help in finding the database character set of an oracle db. Select value from SYS.NLS_DATABASE_PARAMETERS where PARAMETER =..."

BI Applications: Difference Between COALESCE and NVL

BI Applications: Difference Between COALESCE and NVL: "COALESCE and NVL are used to achieve the same functionality of providing a default value in case the column returns a NULL. The differences..."

BI Applications: TO_CHAR(date column) returns zero

BI Applications: TO_CHAR(date column) returns zero: "In Oracle Database we face this issue sometimes. We see that Date field contains all zeros Example - select to_char(Date_column,'dd-mm-yyyy ..."

BI Applications: Unsupported conversion Error for INFA maps on Orac...

BI Applications: Unsupported conversion Error for INFA maps on Orac...: "If your ETL tasks are failing with error as Unsupported conversion Error and log mentions something like below - ORA-26097: unsupported conv..."

BI Applications: Debug the Debugger of INFA

BI Applications: Debug the Debugger of INFA: "Many times we face issues while using the debugger feature of Informatica. We get error saying 'Error connecting to DTM Service' There are..."

BI Applications: DAC - For First Timers

BI Applications: DAC - For First Timers: "DAC is the Datawarehouse Administration Console which is an important component of the OBIA stack. To understand DAC and learn all its feat..."

BI Applications: Oracle BI Applications

BI Applications: Oracle BI Applications: "I have faced this question many times. What is Oracle BI Applications. In simple words its a prebuilt BI solution which can support multip..."

Converting multiple rows into a single comma separated row

Recently, I have been bitten by the MSDN forums bug. I enjoy spending time in the SSAS and SSRS forums, and the learning that I take out from there is tremendous. Initially, I just used to read and learn, while now I reply to a lot of posts too (just the simple ones, keep the tough ones for the experts to solve ;) ).

So the other day, there was this post - how can you convert multiple rows into a single comma separated row? For eg,

This can be done by the FOR XML command.

select State, (
select City + ',' as [text()]
from tableA soi
where soi.State=t.State
order by City
for xml path( '' )
)
from tableA s )t

A handy command, especially because you don't have to use stored procedures to achieve the result.