I am having a real brain freeze with this one, and I know I will be kicking myself...! Instead of concatenating the two columns in the nested SELECT statement, I wish to select them individually in the returned results. For example, I want to change the
following...
SELECT
(
--Fields concatenated using a +
SELECT ISNULL(E.JobTitle, '') + ISNULL(E.EmployerName, '')
FROM Employment E
WHERE C.CustomerID = E.CustomerID
AND E.EmploymentID = 1
)
FROM Report R
INNER JOIN dbo.Proposals P ON R.ProposalID = P.ProposalID
INNER JOIN dbo.Customers C ON C.CustomerID = P.CustomerID
...to the following...
SELECT
(
--Fields separated using a comma
SELECT ISNULL(E.JobTitle, '') , ISNULL(E.EmployerName, '')
FROM Employment E
WHERE C.CustomerID = E.CustomerID
AND E.EmploymentID = 1
)
FROM Report R
INNER JOIN dbo.Proposals P ON R.ProposalID = P.ProposalID
INNER JOIN dbo.Customers C ON C.CustomerID = P.CustomerID
The latter results in the following error:
Msg 116, Level 16, State 1, Line 11
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
SELECT
ISNULL(E.JobTitle, '') , ISNULL(E.EmployerName, '')
FROM Report R
INNER JOIN dbo.Proposals P ON R.ProposalID = P.ProposalID
INNER JOIN dbo.Customers C ON C.CustomerID = P.CustomerID
LEFT OUTER JOIN Employment E
ON C.CustomerID = E.CustomerID
AND E.EmploymentID = 1
-Tab Alleman
Marked as answer by zoggling on Jan 18, 2013 01:03 PM
zoggling
Member
200 Points
316 Posts
Splitting columns within a nested SELECT statement
Jan 17, 2013 03:49 PM|LINK
I am having a real brain freeze with this one, and I know I will be kicking myself...! Instead of concatenating the two columns in the nested SELECT statement, I wish to select them individually in the returned results. For example, I want to change the following...
...to the following...
The latter results in the following error:
Msg 116, Level 16, State 1, Line 11
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Is there an elegant solution to this?
TabAlleman
All-Star
15571 Points
2700 Posts
Re: Splitting columns within a nested SELECT statement
Jan 17, 2013 03:58 PM|LINK
This, maybe?
SELECT ISNULL(E.JobTitle, '') , ISNULL(E.EmployerName, '') FROM Report R INNER JOIN dbo.Proposals P ON R.ProposalID = P.ProposalID INNER JOIN dbo.Customers C ON C.CustomerID = P.CustomerID LEFT OUTER JOIN Employment E ON C.CustomerID = E.CustomerID AND E.EmploymentID = 1nikunjnandan...
Participant
882 Points
223 Posts
Re: Splitting columns within a nested SELECT statement
Jan 18, 2013 05:07 AM|LINK
Hii,
Can you please pass me what exactly output you want?
Like S/W Eng,abc, Accoutant,def Like this??
Please see below link if it helps.
http://nikunjnandaniya.blogspot.in/2012/05/comma-seperated-records-in-sql-without.html
Nikunj Nandaniya
My Blog
zoggling
Member
200 Points
316 Posts
Re: Splitting columns within a nested SELECT statement
Jan 18, 2013 10:10 AM|LINK
Tab - Thank you very much, this seems to do the trick.