时间戳

首页 > 解决方案 > t-sql 将列转置为行

sql-server - t-sql 将列转置为行

问题描述

我这里有 3 张桌子：

这是我到目前为止所拥有的。

这是表格脚本和示例数据：

CREATE TABLE Property 
(
     PropertyID INTEGER NOT NULL PRIMARY KEY
    ,StatusID INTEGER NOT NULL
    ,Country VARCHAR(11) NOT NULL
    ,Town VARCHAR(10) NOT NULL
    ,LastChangedBy VARCHAR(20) NOT NULL
    ,LastChanged VARCHAR(23) NOT NULL
);

INSERT INTO Property (
    PropertyID
    ,StatusID
    ,Country
    ,Town
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    73555
    ,1
    ,'NoMansLand'
    ,'BEEREN'
    ,'Agent009'
    ,'2020-10-15 12:41:14.280'
    );

INSERT INTO Property (
    PropertyID
    ,StatusID
    ,Country
    ,Town
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    72896
    ,6
    ,'Neverlands'
    ,'RIDDERK'
    ,'Agent007'
    ,'2020-08-10 08:41:22.447'
    );
    
CREATE TABLE PropertyAudit 
(
    AID INTEGER NOT NULL PRIMARY KEY
    ,PropertyID INTEGER NOT NULL
    ,StatusID INTEGER NOT NULL
    ,LastChangedBy VARCHAR(20) NOT NULL
    ,LastChanged VARCHAR(23) NOT NULL
)

INSERT INTO PropertyAudit (
    AID
    ,PropertyID
    ,StatusID
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    12340
    ,73555
    ,7
    ,'Agent009'
    ,'2020-10-15 12:41:14.280'
    );

INSERT INTO PropertyAudit (
    AID
    ,PropertyID
    ,StatusID
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    12341
    ,73555
    ,6
    ,'Agent007'
    ,'2020-08-24 08:10:53.223'
    );

INSERT INTO PropertyAudit (
    AID
    ,PropertyID
    ,StatusID
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    12342
    ,73555
    ,5
    ,'Agent009'
    ,'2020-02-13 14:38:10.913'
    );

INSERT INTO PropertyAudit (
    AID
    ,PropertyID
    ,StatusID
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    12343
    ,73555
    ,3
    ,'Agent009'
    ,'2020-02-13 14:33:25.967'
    );

INSERT INTO PropertyAudit (
    AID
    ,PropertyID
    ,StatusID
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    12344
    ,73555
    ,2
    ,'Agent009'
    ,'2020-02-10 13:37:57.527'
    );

CREATE TABLE PStatus 
(
     ID INTEGER NOT NULL PRIMARY KEY
    ,StatusName VARCHAR(20) NOT NULL
    ,LastChangedBy VARCHAR(20) NOT NULL
    ,LastChanged VARCHAR(23) NOT NULL
);

INSERT INTO PStatus (
    ID
    ,StatusName
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    1
    ,'REJECTED'
    ,'dbo'
    ,'2013-05-28 17:02:42.977'
    );

INSERT INTO PStatus (
    ID
    ,StatusName
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    2
    ,'NEW Contract'
    ,'dbo'
    ,'2013-05-28 17:02:42.977'
    );

INSERT INTO PStatus (
    ID
    ,StatusName
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    3
    ,'ACTIVE Contract'
    ,'dbo'
    ,'2013-10-15 12:41:14.280'
    );

INSERT INTO PStatus (
    ID
    ,StatusName
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    4
    ,'MONITOR'
    ,'dbo'
    ,'2013-10-15 12:41:14.280'
    );

INSERT INTO PStatus (
    ID
    ,StatusName
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    5
    ,'DEAL'
    ,'dbo'
    ,'2013-10-15 12:41:14.280'
    );

INSERT INTO PStatus (
    ID
    ,StatusName
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    6
    ,'DEALT'
    ,'dbo'
    ,'2013-10-15 12:41:14.280'
    );

INSERT INTO PStatus (
    ID
    ,StatusName
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    7
    ,'COMPLETED'
    ,'dbo'
    ,'2013-10-15 12:41:14.280'
    );

INSERT INTO PStatus (
    ID
    ,StatusName
    ,LastChangedBy
    ,LastChanged
    )
VALUES (
    8
    ,'ABORTED'
    ,'dbo'
    ,'2013-10-15 12:41:14.280'
    );

这就是我想要达到的目标。

最终输出：

+---------+---------------+----------------+-----------+-------------------------+
| PRD Ref | Latest_Status | Opening_Status | ChangedBy |        ChangedOn        |
+---------+---------------+----------------+-----------+-------------------------+
|   73555 | COMPLETED     | NEW CONTRACT   | Agent009  | 2020-10-15 12:41:14.280 |
+---------+---------------+----------------+-----------+-------------------------+

这是我尝试过的。

如何引入 2 列“Lastest_Status”和“Opening_Status”并从 Status 列复制值。

SELECT prop.[PropertyID] AS "PRD Ref"
    ,(
        SELECT [StatusName]
        FROM [PStatus]
        WHERE pa.StatusID = [ID]
        ) AS "Status"
    ,pa.[LastChangedBy] AS "ChangedBy"
    ,pa.[LastChanged] AS "ChangedOn"
FROM [PropertyAudit] pa
INNER JOIN [Property] prop ON pa.PropertyID = prop.PropertyID
WHERE pa.PropertyID = 73555
ORDER BY pa.PropertyID DESC

+---------+-----------------+-----------+-------------------------+
| PRD Ref |     Status      | ChangedBy |        ChangedOn        |
+---------+-----------------+-----------+-------------------------+
|   73555 | COMPLETED       | Agent009  | 2020-10-15 12:41:14.280 |
|   73555 | DEALT           | Agent007  | 2020-08-24 08:10:53.223 |
|   73555 | DEAL            | Agent009  | 2020-02-13 14:38:10.913 |
|   73555 | ACTIVE Contract | Agent009  | 2020-02-13 14:33:25.967 |
|   73555 | NEW Contract    | Agent009  | 2020-02-10 13:37:57.527 |
+---------+-----------------+-----------+-------------------------+

提前谢谢了。

标签： sql-servertsqlpivotinner-joingreatest-n-per-group

解决方案

这是使用窗口函数和条件聚合的一种方法：

select p.propertyid, 
    max(case when pa.rn_desc = 1 then s.statusname end) last_status,
    max(case when pa.rn_asc  = 1 then s.statusname end) opening_status,
    p.lastchangedby,
    p.lastchanged
from property p
inner join (
    select pa.*, 
        row_number() over(partition by propertyid order by lastchanged) rn_asc,
        row_number() over(partition by propertyid order by lastchanged desc) rn_desc
    from propertyaudit pa
) pa on pa.propertyid = p.propertyid
inner join pstatus s on s.id = pa.statusid
where 1 in (rn_asc, rn_desc)
group by p.propertyid, p.lastchangedby, p.lastchanged

请注意，您实际上并不需要 tableproperty来获得您想要的结果。我们可以进一步挖掘审计表，如下所示：

select pa.propertyid, 
    max(case when pa.rn_desc = 1 then s.statusname     end) last_status,
    max(case when pa.rn_asc  = 1 then s.statusname     end) opening_status,
    max(case when pa.rn_desc = 1 then pa.lastchangedby end) lastchangedby,
    max(case when pa.rn_desc = 1 then pa.lastchanged   end) lastchanged
from (
    select pa.*, 
        row_number() over(partition by propertyid order by lastchanged) rn_asc,
        row_number() over(partition by propertyid order by lastchanged desc) rn_desc
    from propertyaudit pa
) pa
inner join pstatus s on s.id = pa.statusid
where 1 in (rn_asc, rn_desc)
group by pa.propertyid

DB Fiddle 上的演示- 两个查询都产生：

物业编号 | 最后状态 | 开放状态 | 最后更改者 | 最后更改            
---------: | :------------ | :------------- | :------------ | :------------------------
     73555 | 已完成 | 新合同 | 代理009 | 2020-10-15 12:41:14.280

推荐阅读