[Solved] extend current query, calculated columns

Question

Since you have two columns that you now want to PIVOT, you’ll first have to unpivot those columns and then convert those values into the new columns.

Starting in SQL Server 2005, you could use CROSS APPLY to unpivot the columns. The basic syntax will be similar to:

select 
  name,
  new_col,
  total  
from
(
  select name, 
    dt = year(date),
    result,
    total = count(*) over(partition by name)
  from list
) d
cross apply
(
  select 'dt', dt union all
  select 'result', result
) c (old_col_name, new_col)

See SQL Fiddle with Demo. This query gets you a list of names, with the “new columns” and then the Total entries for each name.

|     NAME | NEW_COL | TOTAL |
|----------|---------|-------|
| Person A |    2012 |    11 |
| Person A |       1 |    11 |
| Person A |    2012 |    11 |
| Person A |       2 |    11 |

You’ll see that the dates and the results are now both stored in “new_col”. These values will now be used as the new column names. If you have a limited number of columns, then you would simply hard-code the query:

select name, lost = [1], 
  draw=[2], won = [3], 
  [2014], [2013], [2012], Total
from
(
  select 
    name,
    new_col,
    total  
  from
  (
    select name, 
      dt = year(date),
      result,
      total = count(*) over(partition by name)
    from list
  ) d
  cross apply
  (
    select 'dt', dt union all
    select 'result', result
  ) c (old_col_name, new_col)
) src
pivot
(
  count(new_col)
  for new_col in([1], [2], [3], [2014], [2013], [2012])
) piv
order by [2014];

See SQL Fiddle with Demo

Now since your years are dynamic, then you’ll need to use dynamic sql. But it appears that you have 3 results and potentially multiple years – so I’d use a combination of static/dynamic sql to make this easier:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @orderby nvarchar(max)

select @cols 
  = STUFF((SELECT  ',' + QUOTENAME(year(date)) 
           from list
           group by year(date)
           order by year(date) desc
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @orderby = 'ORDER BY ['+cast(year(getdate()) as varchar(4)) + '] desc'

set @query = 'SELECT name, lost = [1], 
                draw=[2], won = [3],' + @cols + ', Total
            from 
            (
              select 
                name,
                new_col,
                total  
              from
              (
                select name, 
                  dt = year(date),
                  result,
                  total = count(*) over(partition by name)
                from list
              ) d
              cross apply
              (
                select ''dt'', dt union all
                select ''result'', result
              ) c (old_col_name, new_col)
            ) x
            pivot 
            (
                count(new_col)
                for new_col in ([1], [2], [3],' + @cols + ')
            ) p '+ @orderby

exec sp_executesql @query;

See SQL Fiddle with Demo. This gives a result:

|     NAME | LOST | DRAW | WON | 2014 | 2013 | 2012 | TOTAL |
|----------|------|------|-----|------|------|------|-------|
| Person B |    7 |    1 |   2 |    6 |    2 |    2 |    10 |
| Person A |    5 |    3 |   3 |    4 |    3 |    4 |    11 |
| Person C |    2 |    1 |   1 |    3 |    1 |    0 |     4 |

If you want to only filter the result columns for the current year, then you can perform this filtering a variety of ways but the easiest you be to include a filter in the unpivot. The hard-coded version would be:

select name, lost = [1], 
  draw=[2], won = [3], 
  [2014], [2013], [2012], Total
from
(
  select 
    name,
    new_col,
    total  
  from
  (
    select name, 
      dt = year(date),
      result,
      total = count(*) over(partition by name)
    from list
  ) d
  cross apply
  (
    select 'dt', dt union all
    select 'result', case when dt = 2014 then result end  
  ) c (old_col_name, new_col)
) src
pivot
(
  count(new_col)
  for new_col in([1], [2], [3], [2014], [2013], [2012])
) piv
order by [2014] desc;

See SQL Fiddle with Demo. Then the dynamic sql version would be:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @orderby nvarchar(max),
    @currentYear varchar(4)

select @currentYear = cast(year(getdate()) as varchar(4))

select @cols 
  = STUFF((SELECT  ',' + QUOTENAME(year(date)) 
           from list
           group by year(date)
           order by year(date) desc
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @orderby = 'ORDER BY ['+ @currentYear + '] desc'

set @query = 'SELECT name, lost = [1], 
                draw=[2], won = [3],' + @cols + ', Total
            from 
            (
              select 
                name,
                new_col,
                total  
              from
              (
                select name, 
                  dt = year(date),
                  result,
                  total = count(*) over(partition by name)
                from list
              ) d
              cross apply
              (
                select ''dt'', dt union all
                select ''result'', case when dt="+@currentYear+" then result end
              ) c (old_col_name, new_col)
            ) x
            pivot 
            (
                count(new_col)
                for new_col in ([1], [2], [3],' + @cols + ')
            ) p '+ @orderby

exec sp_executesql @query;

See SQL Fiddle with Demo. This version will give a result:

|     NAME | LOST | DRAW | WON | 2014 | 2013 | 2012 | TOTAL |
|----------|------|------|-----|------|------|------|-------|
| Person B |    4 |    0 |   2 |    6 |    2 |    2 |    10 |
| Person A |    2 |    1 |   1 |    4 |    3 |    4 |    11 |
| Person C |    1 |    1 |   1 |    3 |    1 |    0 |     4 |

Accepted Answer