Convert rows to columns in SQL Server by combining 2 columns to form the new column

Question

I'm trying to accomplish something related to Efficiently convert rows to columns in sql server The difference is I have 2 columns which need to form the new column.

So I've used this example from the previously mentioned SO post, because I have 1-N columns: https://data.stackexchange.com/stackoverflow/query/497433

This is what I have so far: https://data.stackexchange.com/stackoverflow/query/1307538

I'm just not able to figure out how I can combine the year/quarter in the 2nd (commented) query.

In the end I would like:

Cols: 2022Q1  2022Q2  2022Q3  2022Q4  2022Q1
Vals: 123     456     345     234     789

Any help would be greatly appreciated!

Answer 1

You need to compute the year/quarter string in the subquery before pivoting. I think the logic you want is:

set @query = 
    n'select ' + @cols + n' from 
    (
        select [count], 
            convert(nvarchar, [year]) + ''q'' + convert(nvarchar, [quarter]) yyyyqq
        from #yourtable
    ) x pivot (
        max([count])
        for yyyyqq in (' + @cols + n')
    ) p';

exec sp_executesql @query;

Answer 2

If you're using a recent version of SQL Server you could use the CONCAT and STRING_AGG function to assemble the query string and then execute dynamically. The data is from your link.

Data

drop table if exists #yourtable;
go
create table #yourtable
    ([Id] int, [Year] int, [Quarter] int, [Count] int);
    
insert into #yourtable([Id], [Year], [Quarter], [Count]) values
    (1, 2022, 1, 123),
    (2, 2022, 2, 456),
    (3, 2022, 3, 345),
    (4, 2022, 4, 234),
    (5, 2023, 1, 789);

Query

declare
  @select         nvarchar(max)=N'select',
  @str1           nvarchar(max)=N' sum(case when [Year]=',
  @str2           nvarchar(max)=N' and [Quarter]=',
  @str3           nvarchar(max)=N' then [Count] else 0 end) as [',
  @str4           nvarchar(max)=N']',
  @from           nvarchar(max)=N' from #yourtable;',
  @sql            nvarchar(max);

select @sql=concat(@select,
                   string_agg(concat(@str1, [Year], @str2, [Quarter], @str3, 
                                     [Year], N'Q',[Quarter], @str4), N','),
                   @from)
from #yourtable

exec sp_executesql @sql;

Output

2022Q1  2022Q2  2022Q3  2022Q4  2023Q1
123     456     345     234     789