Link to home
Start Free TrialLog in
Avatar of Puds32
Puds32Flag for United Kingdom of Great Britain and Northern Ireland

asked on

How can I show the contents of a sub-query in a row of a returned select statement

Dear all
I have two tables client and client_address joined on client_id

I would like to show multiple client_address records in one record from client ie:

                  CLIENT                      CLIENT ADDRESS
record1    james brown            home address
                                                    previous address
                                                    work address

record2    iggy pop                    home address

record3    miles davis               previous address
                                                    work address  

I want to show only one record per client but all available address details with a new line between in the client address column

Thanks for your help

Puds32
Avatar of YZlat
YZlat
Flag of United States of America image

use CHAR(13)
or CHAR(13) + CHAR(10)
try something like

SELECT c.ClientName, ca.HomeAddress + char(13) + char(10)  + ca.PreviousAddress +  char(13) + char(10)  + ca.WorkAddress
FROM Client as c
LEFT JOIN Client_Addres as ca ON c.ClientID=ca.ClientID

Open in new window

SELECT c.ClientName, SUBSTRING(
    (SELECT CHAR(13) + CHAR(10) + ca.ClientAddress
    FROM client_address ca
    WHERE ca.client_id = c.client_id)
    FOR XML PATH(''), 3, 8000) AS Addresses
FROM client c
Avatar of Puds32

ASKER

Thanks for such quick answers

I possibly may have slightly missled:

Home Address, Previous Address and Work Address are not columns in the Client_Address table they are examples of the content of one column (address) in the Client_Address table, so I want to show all the addresses one or more relating to one client in a single cell of the client record and only one record per client

Let me know if clear as mud and I'll try and re-phrase :-)
Puds32,

Did you try my suggestion?  It aligns with your last comment.

Patrick
Avatar of Puds32

ASKER

Hi Patrick
No, I reposted before I saw it, however I've now given it a go but am getting a syntax error
Here's the query using my actual table names

select cbd.family_name, SUBSTRING(
      (select cad.address_type
      from client_address_details cad
      where cad.client_ref = cbd.client_ref)
      FOR XML PATH(''), 3, 8000) as Addresses
from client_basic_details cbd


The error is:
Incorrect syntax near the keyword 'FOR'.

Thanks
Sorry, I had a typo in there with a parenthesis:

select cbd.family_name, SUBSTRING(
      (select CHAR(13) + CHAR(10) + cad.address_type
      from client_address_details cad
      where cad.client_ref = cbd.client_ref
      FOR XML PATH('')), 3, 8000) as Addresses
from client_basic_details cbd

Open in new window

Avatar of Puds32

ASKER

Hey getting there!

The output is as follows:

family_name      Addresses
Black      x0D; Previous Address
 Home Address
Gray      x0D; Home Address
Jones      x0D; Previous Address
 Home Address
Bass      x0D; Home Address
Tenby      x0D; Previous Address
 Previous Address
 Home Address

Couple of formatting issues, any clues, I'm trying to get a new line between the Addresses values
Avatar of Puds32

ASKER

Have done a capture it makes reading a little clearer I hopeUser generated image
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Puds32

ASKER

Thanks Patrick
Just embedded it into ui grid and it works perfectly, brilliant!

You're a star and this site is just the best!

Regards Puds32
Avatar of Puds32

ASKER

Thanks Patrick
Just embedded it into ui grid and it works perfectly, brilliant!

You're a star and this site is just the best!

Regards Puds32