Comma delimited string in a Subquery

Question

Post reply

Comma delimited string in a Subquery

tshad

SSCertifiable

Points: 5905
More actions
June 6, 2012 at 7:03 pm

#262060

Can this be done?
I can get a query to return a comma delimited string of results using Coalesce but it doesn't seem to work as a subquery.
Is there a way to get it to work:
DECLARE @Summary varchar(2000)
SELECT TOP 1000
[ModelId] ,
[GroupId] ,
( SELECT @Summary = COALESCE(@Summary + ', ', '') +
CONVERT(varchar,model.Year) + ' - ' + model.Code
FROM model WHERE model.ModelId = h.modelID
) temp
FROM [History] h
I assume this doesn't work as the subquery doesn't return any values but stores the value in the variable.
Is there a way to get this to work or something like it?
Thanks,
Tom

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

okbangas SSChampion Points: 11773 More actions · Answer 1

Something like this?

SELECT TOP 1000

[ModelId] ,

[GroupId] ,

( SELECT CONVERT(varchar,model.Year) + ' - ' + model.Code + ', '

FROM model WHERE model.ModelId = h.modelID

FOR XML PATH('')

) temp

FROM [History] h

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

FunkyDexter Hall of Fame Points: 3907 More actions · Answer 2

It's not the sub-query that's your problem, it's mixing variable assignment with returning a result set.

You can do this:-

Select MyField From MyTable

Or This:-

Select @MyVar = MyField From MyTable (as long as the select only returns one row)

But not this:-

Select MyField, @MyVar = MyField From MyTable

Niteen.Barhate Valued Member Points: 63 More actions · Answer 3

Here is a good explanation and information to get the CSV from a SQL query or MDX.

Please visit here[/url]

Thanks

Niteen

tshad SSCertifiable Points: 5905 More actions · Answer 4

That worked great, Okbanas.

I actually used this to create a subquery and surround it with a STUFF to get rid of the 1st comma.

Thanks,

Tom

tshad SSCertifiable Points: 5905 More actions · Answer 5

Actuall, Celko, I am not formatting the data on the back end, other than adding a comma to separate the data.

I am getting a summary of child records that the client wants displayed in one cell.

I could have gotten multiple records back for each master record and then run loops on the resulting dataset to gather the child data for each master and then manually build the Grid.

But why do that when there is a nice clean way to use SQL to accomplish the task, which Okbangas came up with. Works great and runs in less than a second.

Sometimes you are right about the formatting but I find that when you can use SQL to do simple formatting for you, the task is simplified and actually easier to understand and maintain.

Your problem is that you are stuck in an intellectual quagmire and can't seem to look at problems and then at other solutions using the tools at hand.

Tom