交叉连接同一表中的N组行

Question

我有一个通用的“ Dimension”和“ DimensionMember”表。

CREATE TABLE [dbo].[Dimension]
(
    [ID] [int] NOT NULL IDENTITY(1, 1),
    [Label] [nvarchar] (255)
)

CREATE TABLE [dbo].[DimensionMember]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[Label] [nvarchar] (255) NOT NULL,
[DimensionID] [int] NOT NULL
) 
GO
ALTER TABLE [dbo].[DimensionMember] ADD CONSTRAINT [FK_DimensionMember_DimensionID_Dimension_ID] FOREIGN KEY ([DimensionID]) REFERENCES [dbo].[Dimension] ([ID])

这些表存储大量维和维成员。

我想从可变数量的维中交叉连接维成员。示例：“性别”，“就业类型”，“合同类型”维度中的交叉联接维度成员应产生以下组合

'Male,Full time, Employee'
'Female,Full time, Employee'
'Male,Part time, Employee'
'Female,Part time, Employee'

'Male,Full time, Contractor'
'Female,Full time, Contractor'
'Male,Part time, Contractor'
'Female,Part time, Contractor'

组合的标签应通过连接维度成员的标签来创建（如上所示）。

先感谢您

更新

维度列表（例如“性别”，“就业类型”，“合同类型”）是DYNAMIC（由运行时的另一个查询生成）。

更新2

修复了一个小错误（维度1->维度）。对不起！

Answer 1

这种模式怎么样？（SQL小提琴）

select a.label+','+b.label+','+c.label
from (select m.label from dimension1 d
  join dimensionmember m
      on m.dimensionid = d.id and d.label = 'sex') a
cross join (select m.label from dimension1 d
  join dimensionmember m 
      on m.dimensionid = d.id and d.label = 'Employment Type') b
cross join (select m.label from dimension1 d
  join dimensionmember m 
      on m.dimensionid = d.id and d.label = 'Contract Type') c

当然，您需要知道要构建多少个子查询，因此需要将SELECT中的串联部分保留多长时间。

编辑

这是一个完成所有任务的工具（更新了SQL Fiddle）

;with base as (
   select m.label, d.id, dense_rank() over (order by d.id) rk
     from dimension1 d
     join dimensionmember m
       on m.dimensionid = d.id
    where d.label in ('sex','Employment Type','Contract Type')
), cte as (
   select cast(label as varchar(max)) list, rk
     from base
    where rk=1
union all
   select cast(cte.list+','+base.label as varchar(max)), base.rk
     from cte
     join base on base.rk=cte.rk+1
)
   select list
     from cte
    where rk=(select max(rk) from base)