SQL query or stored procedure to denormalize and export data

I need to write a query or stored procedure that will get data out of a table and group the data in one cell of a table.  Here is what I need.

Here is the table in the database and sample data

ID    CustomerNumber     AccountID
1     123456                    ABC
2     123456                    DEF
3     654321                    GFK
4     987654                    LKJ
5     987654                    KMN

As you can see there can be multiple account IDs for a Customer number.
My Client wants the data exported to an Excel file in the following format.

Customer Number     Account IDs
123456                    ABC DEF
654321                    GFK
987654                    LKJ KMN

For some reason my client is insistant on having all the account IDs for a Customer number in a sigle cell.

So I am trying to write a query or Stored Procedure to put the data into a Temp table in the format the client wants so I can export it.  Getting the data into a Temp table with the following format will also work.

Customer Number     AccountID_1    AccountID_2   ....
123456                    ABC                 DEF
654321                    GFK
987654                    LKJ                  KMN

The number of account IDs for a customer number can vary.  There is no limit to how many account IDs can be associated with a customer number, but it will probably not exceed 10.  I would guess that I have to use a cusor to step through the records and combine the account IDs into one string for each customer number and then insert that into a temp table.  I just not sure how to go about it.  Or if there is an easier and better way.  I am using SQL Server 2000 and also out-putting the data to an ASP.NET web page.  Thanks for any help.

Andre

at_saintsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
CREATE FUNCTION udf_GetAccounts (@CustomerNumber int)
RETURNS varchar(250)
AS
BEGIN
DECLARE @accounts varchar(250)
SET @accounts = ''
SELECT @accounts = @accounts + AccountID + ',' FROM myTable WHERE CustomerNumber = @CustomerNumber
RETURN SUBSTRING(@accounts, 1, LEN(@accounts) - 1) --strip off the last comma
END

CREATE PROCEDURE sproc_GetAccounts
AS
CREATE TABLE #tmpTable (
     CustomerNumber int,
     Accounts varchar(250)
)

INSERT INTO #tmpTable (CustomerNumber, Accounts) SELECT CustomerNumber, dbo.udf_GetAccounts(CustomerNumber)
     FROM myTable GROUP BY CustomerNumber
GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
caball88Commented:
this is common problem you can use a user defined function to get the results here is one:

CREATE FUNCTION YourUserFuncName(@CustomerNumber int)
RETURNS varchar(2000)

AS
BEGIN

DECLARE @AccountID varchar(2000)
SET @AccountID = ''

SELECT @AccountID=
CASE @AccountID
   WHEN '' THEN ISNULL(AccountID, '')
   ELSE @AccountID + ' ' + ISNULL(AccountID, '')
END
FROM YourTable
WHERE CustomerNumber = @CustomerNumber

RETURN(@AccountID)

END

GO

in your stored proc just call

Select
        CustomerNumber,
        dbo.YourUserFuncName(AccountID)
From
        YourTable
0
at_saintsAuthor Commented:
Thanks for your help.  I tried both solutions and they both worked.  I ended up using a combination of the two functions.  I split the points even.  Thanks for the quick response.  You guys are great.

Andre
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.