Avatar of TimHudspith
TimHudspith asked on

SQL Shaped Recordset: Select two fields but resulting recordset shows them in one field

Is it possible to use SQL to put two fields into one in the resulting recordset?

SELECT Book1, Book2 FROM myTABLE

but somehow Book1 and Book2 end up in another field (myBooks)?

I once read something about shaped recordsets - is this the sort of thing they can do?
Microsoft AccessMicrosoft SQL ServerDatabases

Avatar of undefined
Last Comment
peter57r

8/22/2022 - Mon
Thomasian

hi TimHudspith,

How do you want to combine the two fields? This query will concatenate the 2 fields separated by a comma.

SELECT Book1 & ", " & Book2 As myBooks FROM myTABLE
Thomasian

That syntax is for MS Access. If you are using SQL Server, use +

SELECT Book1 + ', ' + Book2 As myBooks FROM myTABLE
ASKER
TimHudspith

Sorry, I should have been clearer: effectively append Book2 underneath Book1 so I have a list of books in one field.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Thomasian

SQL Server:
SELECT Book1 + char(13) + char(10) + Book2 As myBooks FROM myTABLE

MS Access:
SELECT Book1 & CHR(13) & CHR(10) & Book2 As myBooks FROM myTABLE
peter57r

Do you mean that Book1 and Book2 are different fields in the same record (field names are Book1 and Book2)?
Or are you using 'Book1' and 'Book2' as if they are different records in the table('Book1' is the value in one record and 'Book2' is the value in the same field in another record)?
ASKER
TimHudspith

peter57r: - they are two separate fields. If one has 7 records, the other 3 records, is there a SQL statement that will produce a single field recordset with 10 records?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Thomasian

You will need to create a function to do that. Are you using SQL Server or MS Access? And can you post some sample records and your desired result?
Éric Moreau

I think that the Shaped recordset you mention is specific to ADO: http://support.microsoft.com/kb/189657
peter57r

Why do you want a single field as a result?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
TimHudspith

peter57r: I'm populating a combobox on a form. I know I can create a table of these values, then a recordset thereof; I just thought there may be a nifty little SQL statement out there instead.
peter57r

If you create a single field from all 10 values, you will not be able to select one value from the 10.
You would always be selecting 'the whole field' which is unusable if it contains 10 values.

You need a union statement as your rowsource

Select Book1 from table
Union all
Select Book2 from table
ASKER
TimHudspith

peter57r  - thanks. Out of interest, how do I give the unified field an alias, such as "All Books"?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
peter57r

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question