concatenation of multiple rows text in one column

Question

4.00/5 (1 vote)

See more:

hi,

I have 2 tables.

HTML

Main table name: TransMast, Primary key: TransMastID
Child table name:TransMastDetail, Primary key: TransMastDetailID
foreign key: TransMastID(TransMast)

Against each TransMastID of TransMast, I have many records in TransMastDetails.

for eg. TransMast

HTML

TransMastID  Trans_Name   Trans_Date
     1        abc         10-10-2012
     2        lmn         11-10-2012
     3        adk         12-10-2012

TransMastDetail

HTML

TransMastDetailID  TransMastID  Trans_Text   
   10               1           New         
   11               1           old         
   12               1           Current     
   13               2           Red         
   14               2           Yellow     
   15               2           White         
   16               3           Pen
   17               3           Pencil

Now i want to create a store procedure where I can get all data from main table and one new column called 'Trans_Text' where i want to concatenate all rows of Trans_Text against TransMastID

I want OutPut as:

HTML

TransMastID  Trans_Name   Trans_Date    Trans_Text
   1          abc         10-10-2012    New,old,Current    
   2          lmn         11-10-2012    Red,Yellow,White
   3          adk         12-10-2012    Pen, Pencil

last column should concatenate Trans_Text separated by ',' in one rows with colon.

Posted 10-Oct-12 0:58am

Amu@184

Updated 10-Oct-12 0:59am

Kenneth Haugland

v2

Add a Solution

Comments

Kenneth Haugland 10-Oct-12 7:01am

A tip. Your nicely formatted tables wont be so nice if you dont stick them inside a html tag.

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santhosh Kumar Jayaraman · Accepted Answer · 2012-10-10T01:34:00

try this

SQL

create table transmast
(transmastId int,
trans_Name varchar(50),
trasnd_Date datetime)

create table transmastDetail
(transmastDetailId int,
transmastId int,
trans_Text varchar(50))

insert into transmast values(1,'abc','2012-10-10')
insert into transmast values(2,'lmn','2012-10-11')
insert into transmast values(3,'adk','2012-10-12')

insert into transmastDetail values(10,1,'New')
insert into transmastDetail values(11,1,'old ')
insert into transmastDetail values(12,1,'Current')
insert into transmastDetail values(13,2,'Red')

select transmastId,trans_name,trasnd_Date, STUFF(
               ( select ',' + trans_Text
               FROM transmastDetail 
               WHERE      transmastId=transmast.transmastId
               FOR XML PATH('')), 1, 1, '') AS trans_Text
               from transmast

concatenation of multiple rows text in one column

1 solution

Solution 1

Add your solution here

Preview 0

concatenation of multiple rows text in one column

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us