Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL query or stored procedure to denormalize and export data

Posted on 2005-03-30
3
Medium Priority
?
1,215 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
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

580 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