November 19, 2004 at 7:58 am
I am busy converting all our Crystal reports over to Report Designer and have hit a snag. I have a table that has no unique ID's and contains duplicate data. I need to pull only one of the records but I need to grab additional data from the duplicate rows and concatenate them to into one of the columns which will be returned in the result set.
Table data would look like the following:
CUSTID Initial Phone Group
SmithB BAS 123-1234 ABC
SmithB BAS 123-1234 EFG
SmithB BAS 123-1234 XYZ
KingA AWK 333-1234 UAW
HooverJ JEH 666-1234 WH
I need to return the following
SmithB BAS 123-1234 ABC, EFG, XYZ
KingA AWK 333-1234 UAW
...
What would be the most efficient way to pass thru the table select and concatenate all the groups for a person and return only one row for each person in the table. I generaly create a Stored Procedure for each report that I build for Report Services...
Any ideas would be greatly appreciated
November 19, 2004 at 11:16 am
What if you write a scalar UDF to build the string of groups?
SELECT CUSTID, Initial, Phone, dbo.udf_ConcatGroups( CUSTID ) FROM dbo.Groups
November 19, 2004 at 1:15 pm
While the most efficient way is to do this at the client, SQL Server MVP Adam Machanic has posted some nice example here:
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 24, 2004 at 12:27 pm
Just want to thank you guys for your responses, I tried the UDF route and it worked beautifully...Thanks very much Reg...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply