并排打印结果 SQL Server [英] Print results side by side SQL Server

问题描述

我有以下结果集，

现在有了以上结果，我想通过选择查询打印记录，如下图所示

Now with above results i want to print the records via select query as below attached image

请注意，我将在输出 Present Employee & 中只有两种类型的列.缺勤的员工.

Please note, I will have only two types of columns in output Present Employee & Absent Employees.

我尝试使用数据透视表、临时表，但无法实现我想要的.

I tried using pivot tables, temporary table but cant achieve what I want.

推荐答案

一种方法是 ROW_NUMBER 每个状态"然后使用 FULL OUTER JOIN 将 2 个数据集放入适当的列中.我使用FULL OUTER JOIN，因为我假设您可能有不同数量的员工在场/缺席.

One method would be to ROW_NUMBER each the the "statuses" and then use a FULL OUTER JOIN to get the 2 datasets into the appropriate columns. I use a FULL OUTER JOIN as I assume you could have a different amount of employees who were present/absent.

CREATE TABLE dbo.YourTable (Name varchar(10), --Using a name that doesn't require delimit identification
                            Status varchar(7), --Using a name that doesn't require delimit identification
                            Days int);
GO

INSERT INTO dbo.YourTable(Name, Status, Days)
VALUES('Mal','Present',30),
      ('Jess','Present',20),
      ('Rick','Absent',30),
      ('Jerry','Absent',10);
GO

WITH RNs AS(
    SELECT Name,
           Status,
           Days,
           ROW_NUMBER() OVER (PARTITION BY Status ORDER BY Days DESC) AS RN
    FROM dbo.YourTable)
SELECT P.Name AS PresentName,
       P.Days AS PresentDays,
       A.Name AS AbsentName,
       A.Days AS AbsentDays
FROM (SELECT R.Name,
             R.Days,
             R.Status,
             R.RN
      FROM RNs R
      WHERE R.Status = 'Present') P
    FULL OUTER JOIN (SELECT R.Name,
                            R.Days,
                            R.Status,
                            R.RN
                     FROM RNs R
                     WHERE R.Status = 'Absent') A ON P.RN = A.RN;    


GO

DROP TABLE dbo.YourTable;

db<>fiddle

2 CTE 实际上要整洁得多:

WITH Absents AS(
    SELECT Name,
           Status,
           Days,
           ROW_NUMBER() OVER (ORDER BY Days DESC) AS RN
    FROM dbo.YourTable
    WHERE Status = 'Absent'),
Presents AS(
    SELECT Name,
           Status,
           Days,
           ROW_NUMBER() OVER (ORDER BY Days DESC) AS RN
    FROM dbo.YourTable
    WHERE Status = 'Present')
SELECT P.Name AS PresentName,
       P.Days AS PresentDays,
       A.Name AS AbsentName,
       A.Days AS AbsentDays
FROM Absents A
     FULL OUTER JOIN Presents P ON A.RN = P.RN;

这篇关于并排打印结果 SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！