如何透视表?

2022-01-22 00:00:00 pivot sql tsql sql-server unpivot

我的表有这个结构

subcode date        rol1   rol2 rol3 rol4 rol5 rol6 upto rol60
--------------------------------------------------------------
mc1603  12/03/2011  p      p    a    p    p    p         a
mc1604  12/03/2011  p      p    a    p    p    p         a            
mc1605  12/03/2011  p      p    a    p    p    p         a
mc1606  12/03/2011  p      p    a    p    p    p         a

这里

p=present   
a=absent

这张表会变成

rollno   mc1603 mc1604  mc1605 mc1606 date 
-------------------------------------------------
rol1     p      p       p      p      12/03/2011
rol2     p      p       p      p      12/03/2011
rol3     p      p       a      p      12/03/2011

推荐答案

下面是你如何使用 unpivot 和 pivot 来做到这一点:

Here is how you would do this with both an unpivot and then pivot:

静态查询:

create table #temp
(
    subcode varchar(50),
    date    smalldatetime,
    rol1    varchar(1),
    rol2    varchar(1),
    rol3    varchar(1),
    rol4    varchar(1),
    rol5    varchar(1),
    rol6    varchar(1),
    rol60   varchar(1)
)

insert into #temp values('mc1603', '03/12/2011', 'p', 'p', 'a', 'p', 'p', 'p', 'a')
insert into #temp values('mc1604', '03/12/2011', 'p', 'p', 'a', 'p', 'p', 'p', 'a')
insert into #temp values('mc1605', '03/12/2011', 'p', 'p', 'a', 'p', 'p', 'p', 'a')
insert into #temp values('mc1606', '03/12/2011', 'p', 'p', 'a', 'p', 'p', 'p', 'a')

SELECT p.rollNo, p.[mc1603], p.[mc1604], p.[mc1605], p.[mc1606], p.date
FROM
(
    SELECT *
    FROM
    (
        select subcode, date, rol1, rol2, rol3, rol4, rol5, rol6, rol60
        from #temp
    ) x
    UNPIVOT
    (
        presence FOR rollNo IN([rol1], [rol2], [rol3], [rol4], [rol5], [rol6], [rol60])
    ) unpiv
) x
PIVOT
(
    max(presence)
    FOR subcode IN([mc1603], [mc1604], [mc1605], [mc1606])
) p

drop table #temp

您也可以使用 Dynamic SQL 执行此操作，但您需要获取列列表首先在你可以 unpivot 然后 pivot:

You can also do this with Dynamic SQL but you will need to get the list of columns first before you can unpivot and then pivot:

动态查询:

-- get your roll no columns
DECLARE @cols AS VARCHAR(MAX),
    @query  AS VARCHAR(MAX);

SET @cols = STUFF((SELECT ',' + QUOTENAME(c.Name) 
            FROM sys.columns c
            WHERE OBJECT_ID = OBJECT_ID('dbo.temp') AND c.NAME LIKE 'rol%'
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)') 
        ,1,1,'')

-- get your dynamic string
set @query = 'SELECT p.rollNo, p.[mc1603], p.[mc1604], p.[mc1605], p.[mc1606], p.date
        FROM
        (
            SELECT *
            FROM
            (
                select subcode, date, ' + @cols + ' 
                from temp
            ) x
            UNPIVOT
            (
                presence FOR rollNo IN(' + @cols + ')
            ) unpiv 
        ) x
        PIVOT
        (
            max(presence)
            FOR subcode IN([mc1603], [mc1604], [mc1605], [mc1606])
        ) p'

exec(@query)

结果:

rol1    p   p   p   p   2011-03-12 00:00:00
rol2    p   p   p   p   2011-03-12 00:00:00
rol3    a   a   a   a   2011-03-12 00:00:00
rol4    p   p   p   p   2011-03-12 00:00:00
rol5    p   p   p   p   2011-03-12 00:00:00
rol6    p   p   p   p   2011-03-12 00:00:00
rol60   a   a   a   a   2011-03-12 00:00:00

相关文章