CODEWITHSUNDEEP
sql-server
curious1
curious1

Reputation: 14727

Show 3 different values of the same column and same table in the same row in results

I use SQL Server 2014 for my project. I have three tables:

Table A: 
Column: id
Table B:
Column: id, id_of_Table_A
Table C:
Column: id, id_of_Table_B, category

The relationship between A and B is one to many
The relationship between B and C is one to many

In Table C, there are 10 category values for the category column, but I am interested only in three categories (cat1, cat2, cat3). I hope to query and produce the following results for the three categories:

id_of_Table_A, category, category, category

I have the following statement:

select a.id, c.category from Table_C c 
join Table_B b on b.id = c.id_of_Table_B
join Table_A a on a.id = b.id_of_Table_A
where c.category = 'cat1' and a.id in (1, 2, 3)

But this statement only lists 

id_of_Table_A, cat1

which contains only cat1 of a given id_of_Table_A. I hope to have something like:

1, cat1, cat2, cat3

if cat1, cat2, and cat3 exists in Table C for the given id 1 in Table A.

If a single statement is unable to produce the desired results, then a stored procedure is okay.

Update

In the following example results, 

1, cat1, cat2, cat3

cat1, cat2, cat3 must come from three different records in Table B. Suppose we have these tables and rows:

Table A: 1,2
Table B: (1,1), (2,1), (3,1), (4,2), (5,2), (6,2)
Table c: (1,1,'cat3'), (2,2,'cat1'), (3,3,'cat2'), (4,4,'cat1'), (5,5,'cat4'), (6,6,'cat2')

Then the expected results should be:

1, cat1, cat2, cat3

Basically, the relationships of the tables form a tree. I hope to find the root (Table A) with leaves being cat1, cat2, and cat3 on non-overlapping paths from the root.

Upvotes: 0

Views: 289

Answers (1)

Alex Kudryashev
Alex Kudryashev

Reputation: 9470

You can combine all categories for id_of_b in a single xml value and then show categories in columns. Something like this.

declare @a table(id int)
declare @b table(id int,id_of_a int)
declare @c table(id int,id_of_b int,category varchar(50))
insert @a values(1),(2)
insert @b values(1,1),(2,1),(3,2)
insert @c values(1,1,'cat1'),(2,1,'cat2'),(3,1,'cat3'),(4,2,'cat4')
;with cte as(
  select a.id,
    cast((select category from @c c where c.id_of_b = b.id for xml auto,root,type) as xml) xcat
  from @a a inner join @b b on a.id = b.id_of_a
)
select id,
  t.v.value('c[1]/@category','varchar(50)') cat1,
  t.v.value('c[2]/@category','varchar(50)') cat2,
  t.v.value('c[3]/@category','varchar(50)') cat3
from cte
cross apply xcat.nodes('root') t(v)

Updated answer for updated question

declare @a table(id int)
declare @b table(id int,id_of_a int)
declare @c table(id int,id_of_b int,category varchar(50))
insert @a values(1),(2)
insert @b values (1,1), (2,1), (3,1), (4,2), (5,2), (6,2)
insert @c values (1,1,'cat3'), (2,2,'cat1'), (3,3,'cat2'), (4,4,'cat1'), (5,5,'cat4'), (6,6,'cat2')

;with cte as(
  select a.id,
    cast((select category from @c c inner join @b b on c.id_of_b = b.id 
       where b.id_of_a=a.id
       for xml auto,root,type) as xml) xcat
  from @a a 
)
select id,
  t.v.value('c[1]/@category','varchar(50)') cat1,
  t.v.value('c[2]/@category','varchar(50)') cat2,
  t.v.value('c[3]/@category','varchar(50)') cat3
from cte
cross apply xcat.nodes('root') t(v)

Upvotes: 1

Related Questions