Getting PIVOT using GroupBy

Question

I have this table:

Person      Job     Day  EndDay
1           101     1    12
1           102     2    11
1           103     3    11
3           101     1    11
3           102     2    11 
3           JobOff  3    11
2           101     1    11
2           102     2    11
2           103     3    11
2           JobOff  4    11
...

PS: My days are upto 'N', which I don't know. So, static SQL is not feasible here.

And I need to change it to this form:

Day (Columns)
Person  (Rows)
Person  1    2    3       4
1       101  102  103     N/A
2       101  102  103     JobOff
3       101  102  JobOff  N/A

Any help would be appreciated. I am very new in SQL.

Thanks to Roman, I have this S.Procedure:

CREATE PROCEDURE [dbo].[MyStoredProcedure] AS
BEGIN

declare @stmt nvarchar(max)

select @stmt =
    isnull(@stmt + ', ', '') + 
    'isnull(max(case when [Day] = ' + cast([Day_I] as nvarchar(max)) + 
    ' then Job end), ''N/A'') as [' + cast([Day_I] as nvarchar(max)) + ']'
from (select distinct [Day_I] from dbo.Solution) as t
order by [Day_I]

select @stmt = '
select
    Person_I, ' + @stmt +'
from dbo.Solution_Format
group by Person_I'

END

But when I am executing this stored procedure like this:

exec MyStoredProcedure

Its showing me NO results. No errors!

Answer 1

select
    Person,
    isnull(max(case when [Day] = 1 then Job end), 'N/A') as [1],
    isnull(max(case when [Day] = 2 then Job end), 'N/A') as [2],
    isnull(max(case when [Day] = 3 then Job end), 'N/A') as [3],
    isnull(max(case when [Day] = 4 then Job end), 'N/A') as [4]
from Table1
group by Person

sql fiddle demo

it's also possible to do this with dynamic SQL if you need this to arbitrary number of columns:

create procedure [dbo].[MyStoredProcedure]
as
begin   
    declare @stmt nvarchar(max)

    select @stmt =
        isnull(@stmt + ', ', '') + 
        'isnull(max(case when [Day] = ' + cast([Day] as nvarchar(max)) + 
        ' then Job end), ''N/A'') as [' + cast([Day] as nvarchar(max)) + ']'
    from (select distinct [Day] from Table1) as t
    order by [Day]

    select @stmt = '
    select
        Person, ' + @stmt +'
    from Table1
    group by Person'

    exec sp_executesql
        @stmt = @stmt
end

sql fiddle demo

Answer 2

One way to do this is to use multiple self joins

Select
  t1.Person,
  COALESCE(t1.Job, 'N/A') [1],
  COALESCE(t2.Job, 'N/A' )[2],
  COALESCE(t3.Job, 'N/A') [3], 
  COALESCE(t4.Job, 'N/A') [4]
FROM 
  table1 t1
  LEFT JOIN table1  t2
  ON t1.Person =  t2.Person
    and t2.Day = 2
  LEFT JOIN table1  t3
   ON t1.Person =  t3.Person
     and t3.Day = 3
  LEFT JOIN table1  t4
    ON t1.Person =  t4.Person
     and t4.Day = 4
WHERE
   t1.Day = 1
ORDER BY 
  t1.Person

DEMO

Another ways is to use MAX(CASE however I'm not a fan of using aggregates like this.

Select
  t1.Person,
  COALESCE(MAX(CASE WHEN t1.Day = 1 then t1.Job END), 'N/A') [1],
  COALESCE(MAX(CASE WHEN t1.Day = 2 then t1.Job END), 'N/A') [2],
  COALESCE(MAX(CASE WHEN t1.Day = 3 then t1.Job END), 'N/A') [3],
  COALESCE(MAX(CASE WHEN t1.Day = 4 then t1.Job END), 'N/A') [4]

FROM 
  table1 t1
GROUP BY
  t1.Person

DEMO

And lastly there's the pivot clause which I don't really like because of the MAX here

SELECT person, 
       COALESCE([1], 'N/A') [1], 
       COALESCE([2], 'N/A') [2], 
       COALESCE([3], 'N/A') [3], 
       COALESCE([4], 'N/A') [4] 
FROM   (SELECT t1.person, 
               t1.day, 
               t1.job 
        FROM   table1 t1) p 
       PIVOT (Max (job) 
             FOR day IN ( [1], 
                          [2], 
                          [3], 
                          [4]) ) AS pvt 

DEMO