与动态枢轴连接(版本2) [英] Join with dynamic pivot (version 2)

问题描述

我有一些带有数据的表:

I have some tables with data:

类别

CategoryID     CategoryName

1              Home
2              Contact
3              About

位置

PositionID     PositionName

1              Main menu
2              Left menu
3              Right menu

...(以后可以添加新行)

...(new row can be added later)

CategoryPosition

CPID   CID    PID    COrder

1      1      1      1 
2      1      2      2
3      1      3      3
4      2      1      4
5      2      3      5

如何制作这样的桌子:

CID    CName     MainMenu   LeftMenu   RightMenu

1      Home      1          2          3
2      Contact   4          0          5
3      About     0          0          0

如果以后再添加新的类别"或位置"行，则查询应自动反映更改，例如:

And if a new Category or Position row is added later, the query should reflect the change automatically, e.g:

CID    CName     MainMenu   LeftMenu   RightMenu   BottomMenu

1      Home      1          2          3           0
2      Contact   4          0          5           0
3      About     0          0          0           0
4      News      0          0          0           0

推荐答案

以下动态查询似乎有效:

The following dynamic query seems to work:

declare @columnlist nvarchar(4000)
select @columnlist = IsNull(@columnlist + ', ', '') + '[' + PositionName + ']'
from #Position

declare @query nvarchar(4000)
select @query = '
    select *
    from (
        select CategoryId, CategoryName, PositionName, 
                IsNull(COrder,0) as COrder
        from #Position p
        cross join #Category c
        left join #CategoryPosition cp 
                on cp.pid = p.PositionId 
                and cp.cid = c.CategoryId
    ) pv
    PIVOT (max(COrder) FOR PositionName in (' + @columnlist + ')) as Y
    ORDER BY CategoryId, CategoryName
'

exec sp_executesql @query

一些澄清:

• @columnlist包含根据位置"表构建的动态字段列表
• 交叉连接创建所有类别和所有职位的列表
• 左联接查找对应的COrder
• max()如果有多个类别，则为每个类别+排名选择最高的COrder
• PIVOT()将各种PositionNames转换为单独的列

P.S.我的表名以#开头，因为我将它们创建为临时表.删除#以引用永久表.

P.S. My table names begin with #, because I created them as temporary tables. Remove the # to refer to a permanent table.

P.S.2.如果有人想尝试一下，这里有一个脚本可以创建此问题中的表:

P.S.2. If anyone wants to try his hands at this, here is a script to create the tables in this question:

set nocount on 

if object_id('tempdb..#Category') is not null drop table #Category
create table #Category (
    CategoryId int identity,
    CategoryName varchar(50)
)

insert into #Category (CategoryName) values ('Home')
insert into #Category (CategoryName) values ('Contact')
insert into #Category (CategoryName) values ('About')
--insert into #Category (CategoryName) values ('News')

if object_id('tempdb..#Position') is not null drop table #Position
create table #Position (
    PositionID int identity,
    PositionName varchar(50)
)

insert into #Position (PositionName) values ('Main menu')
insert into #Position (PositionName) values ('Left menu')
insert into #Position (PositionName) values ('Right menu')
--insert into #Position (PositionName) values ('Bottom menu')

if object_id('tempdb..#CategoryPosition') is not null 
    drop table #CategoryPosition
create table #CategoryPosition (
    CPID int identity,
    CID int,
    PID int,
    COrder int
)

insert into #CategoryPosition (CID, PID, COrder) values (1,1,1)
insert into #CategoryPosition (CID, PID, COrder) values (1,2,2)
insert into #CategoryPosition (CID, PID, COrder) values (1,3,3)
insert into #CategoryPosition (CID, PID, COrder) values (2,1,4)
insert into #CategoryPosition (CID, PID, COrder) values (2,3,5)

这篇关于与动态枢轴连接(版本2)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！