在初始表中透视多行

@威尔,这是你需要的逻辑。基本上是感兴趣列上的一个轴函数。

DECLARE @tbl TABLE (RegNo varchar(20), Category varchar(20), Number int)
INSERT INTO  @tbl
SELECT 'R1050162', 'Gym', 1 UNION ALL
SELECT 'R1050162', 'Personal Trainer', 1 UNION ALL
SELECT 'R0093126', 'Group Exercise', 1 UNION ALL
SELECT 'R0143614', 'Yoga Teacher', 1 


SELECT * 
    FROM
@tbl
PIVOT
(
SUM(Number)
FOR Category IN ([Gym], [Personal Trainer], [Group Exercise], [Yoga Teacher]

)
) AS PivotTable;

输出如下:

DECLARE @categories AS NVARCHAR(MAX),
    @your_query  AS NVARCHAR(MAX);

select @categories = STUFF((SELECT distinct ',' + QUOTENAME(Category) 
            FROM your_table 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT RegNo, ' + @categories + ' from 
            (
                SELECT RegNo, Category, Number FROM your_table) tab
                PIVOT
                (
                  SUM(Number) 
                 FOR Category IN (' + @categories + ')
            ) p iv
            ORDER BY piv.RegNo'

execute(@your_query)