Master Detail Record in a One Row

In some cases we need a list of detail records according to master record. i.e. We need a list of detail table’s id for any operation like… updating row or deleting opration. so what we do we just apply join opration on master detail table and get records but not in single row. It is in a different rows.

Today, i need list of detail record according to mater table’s id. First of all i also applied older join method and i got list of record but in a different rows but i need this in single rows.

Finally, i have got solution using googling and it is very simple to take a data in a single row.

You can understand practically using below code. Just copy n paste in sql server analyzer.

First of create a two table. i.e. Master-Detail table. I have explained with university and its affiliated colleges.

[code:sql] — University
create table [dbo].[university](
[universityid] [numeric] (3, 0) NOT NULL PRIMARY KEY,
[universityname] [varchar] (20) NOT NULL
)

— University_affiliated
create table [dbo].[university_affiliated](
[affiliatedId] [numeric] (3, 0) NOT NULL PRIMARY KEY,
[name] [varchar] (20) NOT NULL,
[universityId] [numeric] (3, 0) NOT NULL
)

— Unitersity
insert into [dbo].[university] values(1,’ABC’)
insert into [dbo].[university] values(2,’PQR’)
insert into [dbo].[university] values(3,’MNO’)
insert into [dbo].[university] values(4,’XYZ’)

— University_affilited
insert into [dbo].[university_affiliated] values(1, ‘EFG’ ,1)
insert into [dbo].[university_affiliated] values(2, ‘MNZ’ ,1)
insert into [dbo].[university_affiliated] values(3, ‘XOP’ ,1)

insert into [dbo].[university_affiliated] values(4, ‘EFG’ ,2)
insert into [dbo].[university_affiliated] values(5, ‘MNZ’ ,2)
insert into [dbo].[university_affiliated] values(6, ‘XOP’ ,2)
insert into [dbo].[university_affiliated] values(7, ‘JWO’ ,2)
insert into [dbo].[university_affiliated] values(8, ‘AEC’ ,2)

insert into [dbo].[university_affiliated] values(9, ‘EFG’ ,3)
insert into [dbo].[university_affiliated] values(10, ‘MNZ’ ,3)
insert into [dbo].[university_affiliated] values(11, ‘SEE’ ,3)
insert into [dbo].[university_affiliated] values(12, ‘CFE’ ,3)

insert into [dbo].[university_affiliated] values(13, ‘EFG’ ,4)
insert into [dbo].[university_affiliated] values(14, ‘MNZ’ ,4)
insert into [dbo].[university_affiliated] values(15, ‘XOP’ ,4)
[/code]

University Table:

[code:sql] universityid universityname
————————————— ——————–
1 ABC
2 PQR
3 MNO
4 XYZ [/code]

University_affiliated Table:

[code:sql] affiliatedId name universityId
—————– ——————– —————-
1 EFG 1
2 MNZ 1
3 XOP 1
4 EFG 2
5 MNZ 2
6 XOP 2
7 JWO 2
8 AEC 2
9 EFG 3
10 MNZ 3
11 SEE 3
12 CFE 3
13 EFG 4
14 MNZ 4
15 XOP 4 [/code]

Finally Execute below statement for getting record in a single row as per space seprated.

[code:sql] select universityid,
(
select affiliatedId As ‘data()’
from university_affiliated
where universityId = university.universityId
FOR XML PATH(”)
) As AffiliatedId
from university
[/code]

And see the magical result.

[code:sql] universityid AffiliatedId
—————— ——————————
1 1 2 3
2 4 5 6 7 8
3 9 10 11 12
4 13 14 15[/code]

Using FOR XML PATH (‘ ‘) gives this magical result. Hope you may like it.