不同的行值作为列 Sql Server [英] Distinct Row values as Columns Sql Server

问题描述

我有一个包含 3 列的临时表，如下所示，

I have a temp table with 3 Columns Like below,

    JobID JobType        JobValue
    12    HR              Jesica
    23    MANAGER         Ravi
    5     MANAGER         Jacob
    60    EMPLOYEE      Kiruan
    45    MANAGER         Abidam
    27    HR              Kamsura
    21    MANAGER         Chio Bin
    87    EMPLOYEE      Gamanya
    22    HR              Pradeep
    56    HR              Hari
    67    EMPLOYEE      Om
    14    MANAGER         Kiran




My result table should be like 


    JobID  HR   MANAGER  EMPLOYEE   
    12      
    23      
    5       
    60          
    45      
    27          
    21      
    87          
    22          
    56      
    67          
    14      

Jobvalue 列值应该进入结果集.

Jobvalue column values should come into result set.

我尝试过如下.

创建了一个带有 distict Jobtype 行值的临时表.然后使用 while 循环将 JobValue 列值插入到该表中.

Created a temp table with distict Jobtype row values. then using while loop inseted JobValue column values into that table.

但它看起来很脏.

谁能给我一个很好的建议来完成这个.

Can any one give me a good suggesion to complete this.

谢谢，

推荐答案

试试这个

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(jobtype) 
                    from yourtable
                    group by jobtype
                    ORDER BY jobtype
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
--SELECT @cols
set @query = 'SELECT  JobID,' + @cols + ' from 
             (
                select JobID, jobtype, jobvalue from yourtable
            ) x
            pivot 
            (
                MAX(jobvalue)
                for jobtype in (' + @cols + ')
            ) p '

execute(@query)

这篇关于不同的行值作为列 Sql Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！