一列中多行文本的串联 [英] concatenation of multiple rows text in one column

问题描述

我有2张桌子.

I have 2 tables.

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

针对TransMast的每个TransMastID，我在TransMastDetails中都有很多记录.

例如 TransMast

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

for eg. TransMast

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

TransMastDetail

TransMastDetail

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

现在，我想创建一个存储过程，在这里我可以从主表和一个名为"Trans_Text"的新列中获取所有数据，在这里我想将Trans_Text的所有行与TransMastID连接起来

我希望OutPut为:

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:

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

最后一栏应将冒号"_"连接到Trans_Text中，并用冒号连接成一行.

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

推荐答案

尝试此操作

try this

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 

这篇关于一列中多行文本的串联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！