question

Nagarajan K avatar image
Nagarajan K asked

Concatenate multiple row values into single row

Dear friends, the following query executed on SQL Management studio. But it is not working in Powerbuilder Datawindow source query. i need query for concatenate multiple row values into single row. The following query shows the result in multiple rows. But i need it in single row. SQL Query: select cm_company from cm_companyinfo Result: 000 001 002 003 004 But i change the query like this. so i got result as: select distinct stuff((select ','+ cm_company from cm_companyinfo FOR XML PATH('')),1,1,'') as companylist from cm_companyinfo Result: 000,001,002,003,004 It is working in SQL Management studio only. But in Powerbuilder datawindow sourceit is not working. Actually the "XML PATH" is not supporting. Is there any other option for concatenate the multiple row values into single row in SQL Server? Please advice me soon.. Regards Naga
concatenation
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered
Yes, you can use a cursor, but I wouldn't recommend that, due to the performance implications. DECLARE @A AS VARCHAR(3); DECLARE @B AS VARCHAR(150); SET @b='' DECLARE prelCursor CURSOR LOCAL FAST_FORWARD FOR SELECT yourColumn FROM yourTable ; OPEN prelCursor; -- get first record FETCH NEXT FROM prelCursor INTO @a WHILE (@@FETCH_STATUS = 0) BEGIN SET @b=@b + @a FETCH NEXT FROM prelCursor INTO @a END CLOSE prelCursor DEALLOCATE prelCursor
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

User @a at SSC will get a lot of attention... :)
0 Likes 0 ·
@Håkan Winther yep, we should all know by now that his name is Anthony and he is 34 years old :) Did anybody figure out yet how to bypass this problem? I tried both escaping and entitizing, but no, it still shows up as a hyperlink even in the code block. I suppose that there is a unicode for this character which is not 64 (0x40) but still produces similar character, which could cure the problem, but I don't know the number.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
Can you use your existing code in a view or procedure that you can then call from PowerBuilder? You 'could' use something like this but it is not proven to be 100% accurate as it relies on quirky updates USE [adventureworks] GO DECLARE @fname varchar(1024) SET @fname='' SELECT @fname = [c].[FirstName] + ',' + @fname FROM [Person].[Contact] AS c WHERE LEFT([c].[LastName],3)='fer' AND [c].[MiddleName]='d' SELECT @fname I guess it depends on your requirements - whether this is going into production or being used to generate a one off set of data
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I like the stored procedure idea. That should work nicely with the datawindow, too.
2 Likes 2 ·
@Kenj - a sproc would be my preference to be honest.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.