通过组合 2 列形成新列，将 SQL Server 中的行转换为列 [英] Convert rows to columns in SQL Server by combining 2 columns to form the new column

问题描述

我正在尝试完成与有效地将行转换为sql server 中的列不同之处在于我有 2 列需要形成新列.

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 帖子中的这个例子，因为我有 1-N 列:https://data.stackexchange.com/stackoverflow/query/497433

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

这是我到目前为止所拥有的:https://data.stackexchange.com/stackoverflow/query/1307538

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.

最后我想:

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

任何帮助将不胜感激！

推荐答案

您需要在旋转之前计算子查询中的年/季度字符串.我认为你想要的逻辑是:

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;

这篇关于通过组合 2 列形成新列，将 SQL Server 中的行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！