?
Solved

SQL query or stored procedure to denormalize and export data

Posted on 2005-03-30
3
Medium Priority
?
1,178 Views
Last Modified: 2008-04-15
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

0
Comment
Question by:at_saints
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 total points
ID: 13665569
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
 
LVL 9

Assisted Solution

by:caball88
caball88 earned 500 total points
ID: 13665642
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
 

Author Comment

by:at_saints
ID: 13673831
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

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question