SQL转置枢轴?透视

Question

我有一个表名table1

Partnumber | Model    
12345      | A1    
12345      | B2    
12345      | C3

我希望我的输出是:

Partnumber | Model1 | Model2 | Model3    
12345      | A1     | B2     | C3

模型列N是动态的

任何想法?

Answer 1

您可以使用动态条件聚合:

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql = 
'SELECT
    PartNumber' + CHAR(10) +
    STUFF((
        SELECT DISTINCT
            '   ,MAX(CASE WHEN Model = ''' + Model + ''' THEN Model END) AS ' + QUOTENAME(Model) + CHAR(10)
        FROM Tbl
        FOR XML PATH('')
    ), 1, 0, '') +
'FROM Tbl
GROUP BY PartNumber;';
PRINT @sql;
EXEC sp_executesql @sql;