Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've hit a brick wall and please ask help.

I'm building a table up for (in this example) a ticketing system where e.g. Ticket 1 will allow Parent: Either parent Peter or Sally, Child: to come without a child or with a child (either Sam, Pete or John), Dog: and either without a dog or with one of the dogs (DogMatix,Snowy,Bliksem)

The table layout is that the Parent/Child/Dog is a parameter, and I can get (with CTE) to the Parent/Child relationships to all show, but not the next level showing the dogs too

declare @Ticket table
TID int

declare @Param table
(PID int IDENTITY(1,1) not null,
TID int,
ParamType varchar(20),
Val  varchar(40)

Insert into @Ticket(TID) Select(1)
Insert into @Ticket(TID) Select(2)

Insert into @Param(TID,ParamType,Val) values(1,'Parent','ParentPeter')
Insert into @Param(TID,ParamType,Val) values(1,'Parent','ParentSally')
Insert into @Param(TID,ParamType,Val) values(1,'Child','ChildSam')
Insert into @Param(TID,ParamType,Val) values(1,'Child','ChildPete')
Insert into @Param(TID,ParamType,Val) values(1,'Child','ChildJohn')
Insert into @Param(TID,ParamType,Val) values(1,'Dog','DogDogMatix')
Insert into @Param(TID,ParamType,Val) values(1,'Dog','DogSnowy')
Insert into @Param(TID,ParamType,Val) values(1,'Dog','DogBliksem')

Insert into @Param(TID,ParamType,Val) values(2,'Parent','ParentParker')
Insert into @Param(TID,ParamType,Val) values(2,'Parent','ParentScarlett')
Insert into @Param(TID,ParamType,Val) values(2,'Child','ChildBrett')
Insert into @Param(TID,ParamType,Val) values(2,'Child','ChildBritney')
Insert into @Param(TID,ParamType,Val) values(2,'Child','ChildBronwyn')
Insert into @Param(TID,ParamType,Val) values(2,'Dog','DogRover')
Insert into @Param(TID,ParamType,Val) values(2,'Dog','Dog2')
Insert into @Param(TID,ParamType,Val) values(2,'Dog','Dog3')

;with cte  as (
Select TID,Val as 'Parent' 
from @Param p
where  p.ParamType = 'Parent'
Select cte.TID,cte.Parent,p.Val as 'Child' 
from cte  
left outer join @Param p on p.TID = cte.TID and  p.ParamType = 'Child'

--The required results will be (for ticket 1)
--TID       Parent          Child           Dog
--1     ParentPeter     ChildSam        DogDogMatix
--1     ParentPeter     ChildSam        DogSnowy
--1     ParentPeter     ChildSam        DogBliksem
--1     ParentPeter     ChildPete       DogDogMatix
--1     ParentPeter     ChildPete       DogSnowy
--1     ParentPeter     ChildPete       DogBliksem
--1     ParentPeter     ChildJohn       DogDogMatix
--1     ParentPeter     ChildJohn       DogSnowy
--1     ParentPeter     ChildJohn       DogBliksem
--1     ParentPeter     null            null
--1     ParentPeter     ChildSam        null
--1     ParentPeter     ChildPete       null
--1     ParentPeter     ChildJohn       null

--1     ParentSally     ChildSam        DogDogMatix
--1     ParentSally     ChildSam        DogSnowy
--1     ParentSally     ChildSam        DogBliksem
--1     ParentSally     ChildPete       DogDogMatix
--1     ParentSally     ChildPete       DogSnowy
--1     ParentSally     ChildPete       DogBliksem
--1     ParentSally     ChildJohn       DogDogMatix
--1     ParentSally     ChildJohn       DogSnowy
--1     ParentSally     ChildJohn       DogBliksem
--1     ParentSally     null            null
--1     ParentSally     ChildSam        null
--1     ParentSally     ChildPete       null
--1     ParentSally     ChildJohn       null
share|improve this question
up vote 1 down vote accepted

First off, I think you should consider rethinking your data model a little, and look into some normalization techniques before going ahead with your project.

But, using your model ( only slightly altering it ), you could accomplish what you're after using the following method. Simply add nulls for the child / dog levels and use a simple cross apply query to create the Cartesian product you seem to be after. There's no need for recursive queries, since your data isn't defined recursively. But, this will work :

declare @Ticket table
TID int

declare @EntityTypes table
 id int primary key,
 name varchar(20)

declare @TicketEntities table
(ID int IDENTITY(1,1) not null,
 TicketId int,
 TypeId INT,
 EntityName varchar(40)

Insert into @Ticket(TID) values(1)
Insert into @Ticket(TID) values(2)

insert into @EntityTypes (id, name) values (1, 'Parent');
insert into @EntityTypes (id, name) values (2, 'Child');
insert into @EntityTypes (id, name) values (3, 'Dog');

Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,1,'Peter')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,1,'Sally')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,2,'Sam')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,2,'Pete')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,2,'John')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,2,NULL)
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,3,'Matix')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,3,'Snowy')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,3,'Bliksen')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,3,NULL)

Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,1,'Parker')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,1,'Scarlett')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,2,'Brett')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,2,'Britney')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,2,'Bronwyn')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,2,NULL)
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,3,'Rover')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,3,'Sam')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,3,'Laika')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,3,NULL)

Select par.ticketId, par.EntityName as Parent, child.EntityName as Child, dogs.EntityName as Dog
from @TicketEntities as par
    CROSS JOIN @TicketEntities child
    CROSS JOIN @TicketEntities dogs
    par.TypeId in (select id from @EntityTypes where name = 'Parent') AND par.TicketId = 1
    AND child.TypeId in (select id from @EntityTypes where name = 'Child') AND child.TicketId = 1
    AND dogs.TypeId in (select id from @EntityTypes where name = 'Dog') AND dogs.TicketId = 1
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.