Transact SQL查询-枢轴-SQL

admin

Transact SQL查询-枢轴-SQL

sql

我有MySeekCatTable一个结构表：

       SeekId  CatId

        J       1<= i<=45

在这个桌子上每个SeekId有3个CatId。该表MySeekCatTable引用了两个带有外键的表：

SeekTable具有SeekId主键的第一个表如下所示：

        SeekId  Name

          1     John

          2     Kelly

第二个CatTable具有CatId主键的表如下所示：

        CatId   Name

          1     Cat1

          2     Cat2

我的需求是我必须编写一个查询，以格式给出每SeekId三个查询CatId：

      SeekId      A          B           C

        1         Cat1       Cat2        Cat3

        i         Cati       Catj        Catk

阅读 169

2021-07-01

共1个答案

admin

;WITH cte
     AS (SELECT SeekId,
                CatId,
                ct.Name,
                ROW_NUMBER() OVER (PARTITION BY SeekId ORDER BY CatId) AS RN
         FROM   MySeekCatTable sk
                JOIN CatTable ct
                  ON sk.CatId = ct.CatId)
SELECT SeekId,
       MAX(CASE WHEN RN = 1 THEN Name END) AS A,
       MAX(CASE WHEN RN = 2 THEN Name END) AS B,
       MAX(CASE WHEN RN = 3 THEN Name END) AS C
FROM   cte
GROUP  BY SeekId

2021-07-01