代码之家 › 专栏 › 技术社区 › tkha007

SQL Server-将0或1或2行转换为具有2列的单行的SQL查询

tsql sql-server sql

-1

tkha007 · 技术社区 · 6 年前

我有一个方案如下

Test
--------------------
|   Id    |  Name  |
--------------------
|   1     |  A001  |
|   2     |  B001  |
|   3     |  C001  |
--------------------

RelatedTest
---------------------------------
|   Id    |  Name  |   TestId   |
---------------------------------
|   1     |  Jack  |   NULL     |
|   2     |  Joe   |   2        | 
|   3     |  Jane  |   3        |
|   4     |  Julia |   3        |
---------------------------------

简单解释一下,这个schema RelatedTest有一个可为空的FK要测试,FKId可以出现0次、1次或2次,但不能超过2次。

我正在处理一个t-SQL查询,该查询报告 Test 格式如下

TestReport
---------------------------------------------------------------------------
|   TestId   |   TestName   |   RelatedTestName1   |   RelatedTestName2   |
---------------------------------------------------------------------------
|   1        |   A001       |   NULL               |   NULL               |
|   2        |   B001       |   Joe                |   NULL               |
|   3        |   C001       |   Jane               |   Julia              |

我可以确信 TestReport RelatedTestName不需要超过两列。

这个模式超出了我的控制范围,我只是想查询它以获得一些报告。

我一直试图利用 Pivot 功能,但我不完全确定如何使用它以便 RelatedTestName1 和 相关测试名称1 可以是 NULL 如果没有 RelatedTest 记录。从那以后 RelatedTestName 是一个 varchar 如果需要的话,我不知道如何应用合适的聚合。

0 回复 | 直到 6 年前

san 6 年前

准备数据:

DROP TABLE IF EXISTS Test
GO
CREATE TABLE Test (Id INT PRIMARY KEY, Name VARCHAR(10)) ON [PRIMARY]
GO

INSERT INTO Test Values
(1, 'A001')
,(2, 'B001')
,(3, 'C001')
GO
DROP TABLE IF EXISTS RelatedTest
GO
CREATE TABLE RelatedTest (
  Id INT,
  Name VARCHAR(10), 
  TestId INT FOREIGN KEY REFERENCES Test (Id)
) ON [PRIMARY]
GO

INSERT INTO RelatedTest Values
(1, 'Jack', NULL)
,(2, 'Joe', 2)
,(3, 'Jane', 3)
,(3, 'Julia', 3)
GO

查询:

;WITH CTE AS
(
    SELECT  TestId           = T.Id
            ,TestName        = T.Name 
            ,RelatedTestName = RT.Name
            ,RN = ROW_NUMBER() OVER(PARTITION BY T.Id ORDER BY RT.Id ASC)
    FROM    Test T
            LEFT JOIN RelatedTest RT
              ON T.Id = RT.TestId
)
SELECT  DISTINCT
        C.TestId 
        ,C.TestName
        ,RelatedTestName1 = (SELECT RelatedTestName FROM CTE A WHERE A.TestId = C.TestId AND A.RN = 1)
        ,RelatedTestName2 = (SELECT RelatedTestName FROM CTE A WHERE A.TestId = C.TestId AND A.RN = 2)
FROM   CTE C;