Re: Complicated query

On 12/6/2014 8:28 AM, Shelly wrote:
> I have finally come across a desired query that I have no idea on how to
> do -- or if it is even possible. Here is the scenario:
>
> Table Instances
> ===============
> id
> column1
> column2
> (and more)
>
> Table Questions
> ===============
> Id
> Question Text
>
> Table Answers
> =============
> Id
> QuestionId (Id in Questions)
> InstanceId (Id in Instances)
>
> Table Questions has Q entries. (Lets say just three for now.) What I
> want is to pull where each record contains:
>
> Table Instances has N entries.
>
> Table Answers has Q x N entries.
>
> I want to pull N records where each record has:
> column1, column2, Answer1, Answer2, Answer3
>
> In other words, I want all the answers (Q of them) for a given instance
> to be in a single returned record, not one answer in each of Q records
> returned.
>

After some research I found a good solution on the net. (Note: I inadvertently left out that the Answers table has a column "Answer" when I posed the original question).

This is for Oracle. The solution is:

with data
  as
  (
   select I.*, A.answer, row_number() over (partition by observation_card_id order by answer) rn,

     count(*) over (partition by A.InstanceId) cnt    from Answers A
   join Instances I on (I.ID = A.InstanceId)    order by A.QUESTION_ID
  )
  select D.*, ltrim(sys_connect_by_path(D.Answer,','),',') Answers    from data D
   where rn = cnt
   start with rn = 1
   connect by prior D.Id = D.Id and prior rn = rn-1    order by D.Id

The answers are all in one field, Answeers, and it is a comma separated string.

Thank you to Joe Stefanelli who answered someone else about this on stackoverflow.com.

-- 
Shelly