Transact SQL 查询-Pivot-SQL

发布于 2024-10-24 04:30:49 字数 794 浏览 2 评论 0原文

我有一个具有结构的表 MySeekCatTable：

       SeekId  CatId

        J       1<= i<=45

在此表中，每个 SeekId 都有三个 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

原文

I have a table MySeekCatTable with a structure:

       SeekId  CatId

        J       1<= i<=45

in this table every SeekId has three CatId.
The table MySeekCatTable references two table with foreign keys:

the first table SeekTable with SeekId as primary key is like:

        SeekId  Name

          1     John

          2     Kelly

the second table CatTable with CatId as primary key is like:

        CatId   Name

          1     Cat1

          2     Cat2

My need is that I have to write a query that gives for every SeekId three of its CatId in the format:

      SeekId      A          B           C

        1         Cat1       Cat2        Cat3

        i         Cati       Catj        Catk

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

翻了热茶 2024-10-31 04:30:49

;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

;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

回复收藏 0 原文

~没有更多了~