1

I am really struggling with the following problem as I am fairly new to SQL.

Problem: The projtask table has multiple tasks for 1 project. I need to transpose results so that I show all the statuses (e.g. task 150, 130, 110, 70 status) for every task on a single result row against 1 project.

At the moment I am coming back with multiple result rows against 1 project due to the number of tasks associated with that project. I hope this makes sense. If not please probe. Thanks, all the help would be appreciated :)

Ultimately I want the result to look like:

Project X - Task 10 - Status C - Task 130 - Status A - Task 150 - Status C
Project Y - Task 10 - Status A - Task 130 - Status C - Task 150 - Status A
Project Z - Task 10 - Status C - Task 130 - Status C - Task 150 - Status C


SELECT IIf(dbo_projtask.[task-num]=150 And dbo_projtask.stat='C','Released') AS 150_status, dbo_projtask.[proj-num],
       IIf(dbo_projtask.[task-num]=130 And dbo_projtask.stat='A','Active') AS 130_status
FROM dbo_projtask
GROUP BY IIf(dbo_projtask.[task-num]=150 And dbo_projtask.stat='C','Released'), dbo_projtask.[proj-num],
         IIf(dbo_projtask.[task-num]=130 And dbo_projtask.stat='A','Active');**
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Grant Weir
  • 11
  • 1

1 Answers1

0

Not sure if i understand correctly, but trying to create columns for all tasks under a project doesn't sound like a scalable solution. Why not create an resultset with ProjectID, TaskID and StatusID and do any processing/modifications clietside? Relational databases tend to not like ragged/dynamic columns all that much. If you are absolutely set on the proposed structure you'd need to build a dynamic query that uses a pivot construction of sorts, but have my doubts whether it will work if you have a flexible number of tasks per project.

Elmarg
  • 1