Convert Rows To Columns

1

Hi All,
I've tried reading through to find if an answer already exists, but I'm very new to SQL and am not really sure what I am looking for which made it difficult, I'm sure this is an easy answer but I can't work it out.

I have 2 tables, one holds tasks, linked to this is a sub table with 2 questions per task - I am trying to pull out the tasks with the answers to the questions. I can pull the information out, but I obviously get 2 rows per task - One for each question. I want to convert those into new columns so I have 1 row per task with 2 additional columns generated from the second table and I cannot get it to work. I think I'm close but missing something obvious!

Any assistance would be amazing, here is my current select statement; it generates the 2 columns but it still duplicates the rows per task -

SELECT
TA_SEQ 'Task Ref', TA_CATEGORY 'Category', TA_SHORT_DESC 'Short Description', TA_DUE_DATE 'Reported Date',
CTR_REFERENCE 'Contract',
BG_SITE 'Building'
(SELECT TA_ANS_ANSWER WHERE TA_ANS_QUESTION = 'Question 01?' ) 'Question 01',
(SELECT TA_ANS_ANSWER WHERE TA_ANS_QUESTION = 'Question 02?' ) 'Question 02'

FROM
F_TASKS
INNER JOIN F_CONTRACT ON TA_FKEY_CTR_SEQ=CTR_SEQ
INNER JOIN FLOCATE ON TA_FKEY_BG_SEQ = BG_SEQ
INNER JOIN F_TASK_ANS ON TA_SEQ = TA_ANS_FKEY_TA_SEQ

Thanks in advance.

2

I'm guessing this might work for you:

select t.ta_seq as [Task Ref]
      ,t.ta_category as [Category]
      ,t.ta_short_desc as [Short Description]
      ,t.ta_due_date as [Reported Date]
      ,c.ctr_reference as [Contract]
      ,l.bg_site as [Building]
      ,a1.ta_ans_answer as [Question 01]
      ,a2.ta_ans_answer as [Question 02]
  from f_tasks as t
       inner join f_contract as c
               on c.ctr_seq=t.ta_fkey_ctr_seq
       inner join flocate as l
               on l.bg_seq=t.ta_fkey_bg_seq
       inner join f_task_ans as a1
               on a1.ta_ans_fkey_ta_seq=t.ta_seq
              and a1.ta_ans_question like 'Question 01%'
       inner join f_task_ans as a2
               on a2.ta_ans_fkey_ta_seq=t.ta_seq
              and a2.ta_ans_question like 'Question 02%'
;
1 Like
3

Worked perfectly, thanks!