sqlserver 行转列 示例

declare @server nvarchar(1024);

with tt as(
SELECT
       tjdate ,
       server_p ,
       newusernum 
        FROM qpinfo.dbo.tjdata_hour_ziyan WHERE hour=23   and tjdate=CONVERT(char(10),getdate()-1,121) and server_p!=''
)
SELECT @server= STUFF(
    (
        SELECT '], [' + server_p
        FROM tt
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '');
    SELECT @server=@server+''''
    
    select @server=SUBSTRING(@server,2,len(@server)-2)+']'
    declare @sql nvarchar(4000)
    
    select @sql='
 with tt as(
SELECT 
       tjdate ,
       
       server_p ,
       newusernum 
        FROM qpinfo.dbo.tjdata_hour_ziyan WHERE hour=23  
)
select * from tt as src pivot(
sum(newusernum) for server_p in('+@server+')
) as pvt;'
select @sql
exec(@sql)