Перекрестное соединение 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'

Метки комбинаций должны создаваться путем объединения меток элементов измерения (как показано выше).

заранее спасибо

ОБНОВИТЬ

Список параметров (например, «Пол», «Тип занятости», «Тип контракта») является ДИНАМИЧЕСКИМ (созданный другим запросом во время выполнения).

ОБНОВЛЕНИЕ 2

Исправлена ​​небольшая ошибка (Dimension1 -> Dimension). Извиняюсь!


sql sql-server join cross-join
person Maxim Eliseev    schedule 26.09.2012    source источник

Ответы (1)


arrow_upward
5
arrow_downward

Как насчет этого узора? (скрипт 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)
person RichardTheKiwi    schedule 26.09.2012
comment
Это смутило меня, потому что вы поместили ограничение в предложение соединения, в то время как я ожидал увидеть его в предложении where. - person Neil; 26.09.2012