交叉连接同一表中的N组行 [英] Cross join N sets of rows in same table

问题描述

我有一个通用的"Dimension"和"DimensionMember"表.

I have a generic "Dimension" and "DimensionMember" tables.

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

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

These table store a large number of dimensions and dimension members.

我想从可变数量的维中交叉连接维成员. 例如:性别"，就业类型"，合同类型"维度中的交叉联接维度成员应产生以下组合

I want to cross join dimension members from variable number of dimensions. Example: cross join dimension members from 'Sex','Employment Type','Contract Type' dimension should produce the following combinations

'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'

应该通过合并维成员的标签来创建组合的标签(如上所示).

The labels of the combinations should be created by concatenating labels of dimension members (as shown above).

提前谢谢

维度列表(例如性别"，就业类型"，合同类型")是DYNAMIC(在运行时由另一个查询生成).

A list of dimension (e.g. 'Sex','Employment Type','Contract Type') is DYNAMIC (produced by another query at run-time).

修复了一个小错误(维度1->维度).抱歉！

Fixed a small error (Dimension1 -> Dimension). Sorry!

推荐答案

这种模式怎么样? ( SQL小提琴)

How about this pattern? (SQL Fiddle)

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中的串联部分保留多长时间.

Granted you need to know how many subqueries to build and therefore how long the concatenation part in the SELECT needs to be.

编辑

这是一个完成所有任务的工具(已更新 SQL小提琴)

And here's one that does it all (updated 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)

这篇关于交叉连接同一表中的N组行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！