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;