sql - Pivot multiple rows in initial table

Question

I have a table which i would like to pivot to show how many categories a person is affiliated with...

I would like to pivot this to show:

There are a lot more members and categories but the theory i believe should be the same.

I have attempted this however it only shows the first line for each.

Thanks in advance

Will

标签： sqltsqlpivot

Answer 1

您只需要使用 PIVOT 函数中的 Number 字段求和，就可以获得一个类别列表：

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)