0

I have a data set that I would like to represent sort of like a pivot table. My challenge is the system that I am working is does not support pivot, cursor or any variables\temp tables, it has to be done in a relatively basic query.

So my data looks like this.

Email          Project    EndDate    EstHours    ActualHours
-----------------------------------------------------------
joe@gmail.com  Proj 1     03/1/2018   10          12
joe@gmail.com  Proj 1     03/8/2018   8           10
joe@gmail.com  Proj 1     03/14/2018  13          17
jay@gmail.com  Proj 1     03/1/2018   10          12
jay@gmail.com  Proj 1     03/8/2018   8           10
jay@gmail.com  Proj 1     03/14/2018  13          17

I would like my output to look like this

Email           Project   03/1/2018  03/08/2018 03/14/2018
-----------------------------------------------------------
joe@gmail.com   Proj 1      10,12       8,10      13,17
jay@gmail.com   Proj 1      10,12       8,10      13,17

In this example I only show 3 weeks worth of estimates but I have available up to 52 weeks. Is there a way to transform this data whilst selecting the number of weeks that I would like to show? Hard coding the number of weeks if fine.

Thanks

Jeremy Ragsdale
  • 95
  • 8
  • 6
    That is called _dynamic pivot_. Super-duper-duplicate question. – Ivan Starostin Mar 12 '18 at 14:09
  • 1
    Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) – Ryan Gadsdon Mar 12 '18 at 14:15
  • 2
    What do you mean it doesn't support "pivot, cursor or any variables\temp tables"? You are using sql server right? And how exactly would you expect to convert normailzed data into a denormalized mess with a "relatively basic query"? – Sean Lange Mar 12 '18 at 14:16
  • Our system uses SQL as it's data store but where we write the queries we have limitations. I would prefer this not be the case but it is what it is. – Jeremy Ragsdale Mar 12 '18 at 15:18
  • Sorry all, I agree that if I were able to use Pivot or other dynamic features that, yes this would be a super-duper-duplicate question. Unfortunately my system allows me to write queries but I am limited by what functionality is exposed to me. We can use most SQL except, cursors, temp tables, variables. In fact out system has validation that requires that a query begin with the select keyword. – Jeremy Ragsdale Mar 13 '18 at 15:16
  • Please update your question adding the limitations you posted in the comments – Andrea Mar 18 '18 at 10:44
  • I think I did state that in both the question and in my supporting comments? To quote my last comment - "We can use most SQL except, cursors, temp tables, variables. In fact out system has validation that requires that a query begin with the select keyword". Sorry if that was not clear originally. – Jeremy Ragsdale Mar 19 '18 at 14:32

0 Answers0