pivot

实现动态行转列

DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX);
if object_id('tempdb..##tmp1') is not null
begin
        DROP TABLE ##tmp1 
end
if object_id('tempdb..##tmp2') is not null
begin       
         DROP TABLE ##tmp2
end
;
with tmpdata as 
(      
        select  N'车线' as Id    
        union 
        select  N'刀模'as Id
        union 
        select  N'电压模' as Id
        union 
        select  N'扣凿模'as Id     
)
select *  into ##tmp1 from  tmpdata
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.id) 
            FROM ##tmp1 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT  DATAAREAID, PRICECALCID, ' + @cols + ' into ##tmp2 from  
            (
                select PRICECALCID
                    , convert(decimal(16, 4),Price) as Price
                    , DATAAREAID
                    ,MisItemName
                from TEC_BOMCostMisCalc 
           ) x
            pivot 
            (
                sum(Price)
                for MisItemName in (' + @cols + ')
            ) p '
execute(@query)
select * from  ##tmp2