我有MySeekCatTable一个结构表:
MySeekCatTable
SeekId CatId J 1<= i<=45
在这个桌子上每个SeekId有3个CatId。该表MySeekCatTable引用了两个带有外键的表:
SeekId
CatId
SeekTable具有SeekId主键的第一个表如下所示:
SeekTable
SeekId Name 1 John 2 Kelly
第二个CatTable具有CatId主键的表如下所示:
CatTable
CatId Name 1 Cat1 2 Cat2
我的需求是我必须编写一个查询,以格式给出每SeekId三个查询CatId:
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