Contact Us

 Bharat Patel     Feb,08 2017

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.

 -- Universitycreate table [dbo].[university]([universityid] [numeric] (3, 0) NOT NULL PRIMARY KEY,[universityname] [varchar] (20) NOT NULL)-- University_affiliatedcreate table [dbo].[university_affiliated]([affiliatedId] [numeric] (3, 0) NOT NULL PRIMARY KEY,[name] [varchar] (20) NOT NULL,[universityId] [numeric] (3, 0) NOT NULL)-- Unitersityinsert 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_affilitedinsert 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)

University Table:

 universityid universityname--------------------------------------- --------------------1 ABC2 PQR3 MNO4 XYZ 

University_affiliated Table:

 affiliatedId name universityId----------------- -------------------- ----------------1 EFG 12 MNZ 13 XOP 14 EFG 25 MNZ 26 XOP 27 JWO 28 AEC 29 EFG 310 MNZ 311 SEE 312 CFE 313 EFG 414 MNZ 415 XOP 4 

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

 select universityid,(select affiliatedId As 'data()'from university_affiliatedwhere universityId = university.universityIdFOR XML PATH('')) As AffiliatedIdfrom university

And see the magical result.

 universityid AffiliatedId------------------ ------------------------------1 1 2 32 4 5 6 7 83 9 10 11 124 13 14 15

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