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

我有一个通用的“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'

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

先感谢您

UPDATE

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

UPDATE 2

修复了一个小错误（Dimension1 -> Dimension）。对不起！

这个图案怎么样？ (SQL小提琴 http://sqlfiddle.com/#!3/8c512/5/0)

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 中的串联部分需要多长。

EDIT

这是一个可以完成这一切的功能（已更新SQL小提琴 http://sqlfiddle.com/#!3/8c512/12/0)

;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)