Cross join N sets of rows in same table

Question

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

Thank you in advance

UPDATE

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

UPDATE 2

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

Answer 1

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

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

---

EDIT

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)