Transact SQL查询-枢轴-SQL [英] Transact SQL Query-Pivot-SQL

问题描述

我有一个表MySeekCatTable，其结构如下:

I have a table MySeekCatTable with a structure:

       SeekId  CatId

        J       1<= i<=45

在此表中的

中，每个SeekId具有三个CatId. 表MySeekCatTable引用两个带有外键的表:

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

以SeekId作为主键的第一个表SeekTable如下:

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

        SeekId  Name

          1     John

          2     Kelly

以CatId作为主键的第二张表CatTable如下:

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

        CatId   Name

          1     Cat1

          2     Cat2

我的需求是我必须编写一个查询，该查询为每个SeekId的CatId中的三个提供以下格式:

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

推荐答案

;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  

这篇关于Transact SQL查询-枢轴-SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！