One to many concatenated column

[NoCoolHandle]

did it mention a line number?
was it 6 or 9?

 

[jbellCNS]

9

 

[jbellCNS]

I should clarify:

This is the contents of the pane I am executing:

DECLARE @str VARCHAR(100)
(SELECT @str = COALESCE(@str + '', '') + CONVERT(VARCHAR, [WINDOW_START]) FROM ANC_IND_HIST)

DECLARE @dates VARCHAR(50) 
select TOP 100
[INDIVIDUALS 2].IND_ID,
[INDIVIDUALS 2].Ind_Full_Name,
(select anc_ind_hist.window_start) AS plan_window_start,
(SELECT @str = COALESCE(@str , '') + CONVERT(VARCHAR, [WINDOW_START]) FROM ANC_IND_HIST)
/*(SELECT @str = COALESCE(@str + '', '') + [WINDOW_START]) as windows_start */

FROM [individuals 2]
LEFT JOIN ANC_IND ON ([individuals 2].IND_ID = ANC_IND.IND_ID)
LEFT JOIN ANC_IND_PLAN ON (ANC_IND.IND_ID = ANC_IND_PLAN.IND_ID)
LEFT JOIN ANC_IND_HIST ON (ANC_IND.IND_ID = ANC_IND_HIST.IND_ID)

This is the exact error message I am receiving:

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '='.

 

[Qik3Coder]

You cannot mix an assignment, which you have in the first,
with a result set, such as you have in the second.

Why isn't this:

(select anc_ind_hist.window_start) AS plan_window_start,

just this:

anc_ind_hist.window_start plan_window_start,

Lodlaiden

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[gmmastros]

I would encourage you to read this:
thread183-1159740

In that thread, it talks about a hidden gotcha for this type of query (if there are nulls), and shows how to make a user defined function to return the data you are looking for.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbellCNS]

Thank you all for your input. I believe we have decided on a solution that will not necessitate this functionality in the SQL by moving the responsibility to the server side scripting.