Concatenate multiple rows into one column?

Newbie question here. I have two tables that have a one to many relationship. I want to create a query that takes the info from the child table (possibly multiple rows) and concatenates it into a single column in the parent table. The tables are:

TableParent (ASSIGNNUM (PK), DESC, STARTDATE)
TableChild (ASSIGNNUM (FK), EMPLOYEENUM)

There could be multiple employees for each assignment. Sample data:

TableParent
1....First Assignment....05/01/2008
2....Second Assignment...05/03/2008
3....Third Assignment....05/07/2008

TableChild
1....55342
2....33456
2....52343
3....35225
3....45121
3....11553

I would like the query result to look like this:

1....First Assignment....05/01/2008....55342
2....Second Assignment...05/03/2008....33456,52343
3....Third Assignment....05/07/2008....35225,45121,11553

Any suggestions would be appreciated!

0
lwhalen618
5/8/2008 1:36:43 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

5 Replies
1144 Views

Similar Articles

[PageSpeed] 19

Hello,

I found this post to be useful in your case

http://forums.devshed.com/ms-sql-development-95/concatenate-one-field-from-multiple-rows-394621.html

 

Regards 


ASP.NET Hosting (HostingFest)
0
smiling4ever
5/8/2008 3:10:58 AM

see the example, 

create table Employee

(

EmployeeID int identity(1,1),

EmpName varchar(200)

)

create table EmpProjects

(

EmpProjectID int identity(1,1),

EmployeeID int,

ProjName varchar(50)

)

insert into Employee values ('ram')

insert into Employee values ('reddy')

insert into EmpProjects values (1,'a')

insert into EmpProjects values (1,'b')

insert into EmpProjects values (2,'c')

insert into EmpProjects values (2,'d')

 

select distinct EmpName ,(

select ProjName + ','

from EmpProjects EP

where EP.EmployeeID = E.EmployeeId

for xml path('')) as Projects

from Employee E


Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
0
ramireddyindia
5/8/2008 4:45:00 AM

Hello Iwhalen,

You can review the article at Get column values as comma seperated list using XML PATH() instead of UDF's using COALESCE for concatenation of row values as one string value and also there is a second approach discussed at Using COALESCE function

Eralper

 


http://www.eralper.com
http://www.kodyaz.com
0
eralper
5/8/2008 5:02:57 AM

I need to do this using an access database. Does anyone have any ideas on how to do this?

Thanks

 J
 

0
jd65pl
5/8/2008 4:55:32 PM

I used a ConcatOrderProducts function found in the following post to solve my problem. It worked perfectly!  

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53293

Thanks for your help everyone. Lisa

0
lwhalen618
5/13/2008 3:12:35 AM
Reply:

Similar Artilces:

SQL: mutiple rows one column to multiple columns one row
In InfoMaker SQL can you take a table with multiple rows for the same key and take a column from the rows and create mutiple columns with one report line with the key(no duplicate keys) on a report? For example Table books (key) (book title) 123 misery 123 titanic 123 roots 456 war 456 1984 456 giant The report I need would look like this Key title1 title2 title 3 123 misery titanic roots 456 war 1984 giant Thanks in advance. -- Kaye Hendry HealthInsight email:kaye.hendry@healthinsight.org ...

SQL,Make Multiple values for multiple columns into one column separated by comma
Hi, I'm getting the result as this Name       Class       Subject------------------------------------------Anju         10             MathsAnju         10             Physics How to write the query to get the result like this Name       Class       Subjects------------...

is it possible to turn 1 column,many rows into one row,many columns in sql ?
 Hi AllI have the following query:Select CategoryName From Category Where CategoryId In(Select SkillId From SpecialitySkills Where CopywriterId = 13) It gives me the correct results but not in the form that i want, i would like it to be in one row, many columns. its in 1 column, many rows.Any ideas?  You must be talking about displaying it on a web page? Just create a loop that runs through the rows of the data that is returned then just make html table:dim tablestring as string = "<table><tr>" for i as integer = 0 to dt.rows.count - 1 tablestring += &...

concatenate multiple rows in one row
i have a table below id    text1    text2 -------------------------------118   id12    text123 7118   4     retre 8118     3    erwetre    i need to convert the rows to columns in sql server 2000(pivot in 2005 but i dont want in 2005) to id  text1   text2            text1        text2   text1       &...

Display multiple columns in Gridview from One Column of SQL table
Hi I have a table in sql with 2 columns and I want to display that in multiple columns in Gridview. SQL Table No.            Type 1               A1 1               B2 2              A2 2               B2 3        &...

Gridview
Hi. What I would like to do is to have a layout like this ID Description SomeOthercolumn ----------------------------------------------------------------------------- ID Short description Text Some other value Long description Text that will span multiple columns ID #2 Short description Text #2 Some other value #2 Long description Text that will span multiple columns #2 Get the picture. This is not a problem with repeaters, because you ha...

concatenate a column from multiple rows
Hello, I am not sure if its right to post it here, I would like to know the SQL to concatenate a column from multiple rows in an object?? I am using TransactSQL. I have an object with below records id name --- ---- 1 raj 2 abc 3 xyz 1 red 2 green 3 pink I need the ourput like below id name --- ---- 1 raj,red 2 abc,green 3 xyz,pink Thanks in advance Raj <Raj> wrote in message news:4c5f656b.2e5f.1681692777@sybase.com... > Hello, > > I am not sure if its right to post it here, I would like >...

Inserting Multiple Rows in One SQL
Hi, would like to know if there are any links or sample code to learn how to Insert multiple rows with 1 sql statement.Also, can the inserted values' source be from a table in another database table or from a dataset?I am actually trying to insert about 117 rows of data.Table 1======UID               Primary Key TeamCode      a code value representing different teams Week              will equal to 2Points  &n...

query that concatenates multiple rows into one
e.g. If the data rows in a single table are - a    1 a    2 a   3 b   1 b   4 the query output should be like this a   1      2      3 b   1      4 Please help me regarding this, Thanks Can you reformulate your question? .. I didn't understand what you want.  Rod H If there is a table 'table1' with two columns 'col1' and 'col2', which looks like as shown in above, I want to write a query that, so that the result s...

Binding Multiple Rows to One DataGrid Column
Hi, I need to display the content of database columns a, b, c, d in one DataGrid row by inserting break line between them. Columns b, c and d are somethimes empty and I dont want to insert break line if they are empty. Can anyone please help me how to do this... I have something like this so far..... Dim Col1 As New BoundColumn Col1.DataField = ("a") & ("b") ---> it doesnt work... MyDataGrid.Columns.Add(Col1)   Thanks  Zumbul wrote: I need to display the content of database columns a, b, c, d in one DataGrid row by inserting break line between them Can...

Concatenate varchar columns across multiple rows
Hi all, Is it possible in SQL to use the GROUP BY clause (or some other built in SQL clause) to SUM not only numbers but also strings so the result is a concatenated string. So the query might look like...SELECT myGroupCode, SUM(Surname + ', ') As ConcatenatedSurnames FROM tblPeople GROUP BY myGroupCodeThe result returned in column ConcatenatedSurnames is..."Jordan, Bird, Johnson, Erving"Is there a simple way of doing this?Thanks for your time.  select distinct myGroupCode, stuff((select ',' + SurName from tblPeople where myGroupCode = t.myGroupCode),1,1,...

Filter one datasource column with multiple values
I've done some poking around, but didn't see an answer on this. I have a GridDataSource that I want to filter when the page loads. But I want to filter a column by multiple values, like using a "contains" or an "in". The column is an integer value. I tried this: protected void GridDataSource_Selecting(object sender, LinqDataSourceSelectEventArgs e) { if (someCondition) { using (MyDBContext db = new MyDBContext ()) { e.Result = db.Employees.Where(emp => _currentUser.ShiftIds.Contains(emp.ShiftID)); } ...

update multiple columns in one sql statements
I am almost sure I can update variables columns in one select/case type statement, but having problems working out the syntax. I have a table with transactions - with tran types as the key. in this example, types = A,B,C ,D. in this first example I am updating the sum of QTY to value t_A based on tran types =A. can I perform sub query/case to update with the same where clause but for types B,C and D?? I also have to insert for specific lot numbers each sum values. Create table #t_reconcile( t_lot_number int not null, t_A ...

SQL Multiple values from one column query?
Hello, Im trying to make a query that returns data like so:I have the following columns; Name, Description, Link, Year, Sketched, Colouredin which Year is an Int, Sketched and Coloured are boolean and the rest are varchar(xx). Now I want a query in which I can choose to get something with the following values: Year either 2007 or 2008 , Sketched either true or false and Coloured is true.I tried something like this: SELECT Name, Description, Link, Year, Sketched, Coloured FROM ImageTabe WHERE (Year = @Year1 OR Year = @Year2 OR ...

Web resources about - Concatenate multiple rows into one column? - asp.net.sql-datasource

concatenate - Online Journalism Blog
Posts about concatenate written by Paul Bradshaw

Concatenate: The Ultimate Excel Function for Managing PPC Campaigns
... build URL tags. There are lots of Excel functions out there that can make managing and reporting on PPC campaigns easier. My favorite is Concatenate ...

Resources last updated: 1/19/2016 10:42:28 AM