Puds32
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
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
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
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
(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
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 :-)
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
Did you try my suggestion? It aligns with your last comment.
Patrick
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Just embedded it into ui grid and it works perfectly, brilliant!
You're a star and this site is just the best!
Regards 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
Just embedded it into ui grid and it works perfectly, brilliant!
You're a star and this site is just the best!
Regards Puds32