473,381 Members | 1,452 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,381 software developers and data experts.

Join multiple records into one field

Is there a way to create one field from multiple records using sql.
For example

Table 1
John 18
Peter 18
David 18

Now I want an sql query that when executed will return a field that
looks like this

Query1
John Peter David

So basically it will return one record with all the name in one field

May 4 '06 #1
4 9926
Here is the answer for your question

select a.column1,
(select column2 from table where column1=a.column1),
(select column2 from table where column1=a.column1),
(select column2 from table where column1=a.column1)
from Table1 a

You need to have a single column in your table which can build
recursive relation.

May 4 '06 #2
Thanks for replying but I am getting the following error "single-row
subquery returns more than one row "

May 4 '06 #3
Terren (ca**************@gmail.com) writes:
Is there a way to create one field from multiple records using sql.
For example

Table 1
John 18
Peter 18
David 18

Now I want an sql query that when executed will return a field that
looks like this

Query1
John Peter David

So basically it will return one record with all the name in one field


If you are on SQL 2000, you need to run a cursor over the table. There
is no defined method that gives you the result in a single statement.
You do things like SELECT @x = @x + col FORM tbl, but the correct result of
this operation is undefined, so you cannot be sure on what you get.

On SQL 2005, you can use the XML stuff to get there. Here is a
sample query for the Northwind database:

select CustomerID,
substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarchar(30), OrderID) + ',' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('')) as Dummy(OrdIdList)
go
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 4 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: StealthBananaT | last post by:
My database has two tables - films has 10,000 records and reviews has 20,000 records. Whenever I try to list all the films and the count of its reviews, MySQL locks and I have to restart the...
0
by: Petre Agenbag | last post by:
Hi List Me again. I'm trying to return from multiple tables, the records that have field "information_sent" between two dates. The tables are all related by means of the id of the entry in the...
6
by: Christopher Harrison | last post by:
Is there a way to store an indefinite number of keys in one field and self join them? Imagine, for example, you have a table of users with a "friends" column. Say user 1 is friends with users 9, 7,...
7
by: Pino | last post by:
Hi, to everybody, let's consider this scenario: you have 1 data-table and 10 dictionary-tables; the data-table has 5 million records and 30 columns, 10 of these columns have a foreign-key to the...
9
by: Alan Lane | last post by:
Hello world: Background: Yesterday, January 21, Doug Steele was kind enough to help me out on a Left Join problem. I was trying to return all stores and their Gross Adds for December, 2004...
2
by: Darryl Kerkeslager | last post by:
The following SQL does as I intend, but ... SELECT offender_id, off_name, inv_ppo_id, add_note, add_zip_id, it_name AS Purpose FROM (offender INNER JOIN (investigation INNER JOIN...
52
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible...
3
by: tdes42 | last post by:
I believe I am looking for some form of Join Query, but my understanding of Access logic and my logic do not yet click entirely…. I have a table of ocean buoy data, taken every hour over many...
1
by: Nettle | last post by:
Purpose: This is a Distribution List database. Function: Users create many different email distribution lists, tailoring each to fit their specific needs Wanted: Users can combine...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.