How to display multiple donations instances from a single donor in one row rather than multiple rows

Question

How to display multiple donations instances from a single donor in one row rather than multiple rows

jpwiley

Grasshopper

Points: 11
More actions
August 11, 2008 at 3:47 pm

#70636

I am just getting started with SQL programming and I am having trouble with a query I am working on. I work for a non-profit and am trying to get details about donor giving history for the year.
What I would like to do is to see all the gifts (gift date, amount, project code and description) on one row rather than multiple rows. This will allow me to build a mail merge from my results.
Maybe it would help to describe what I am getting and then explain what I would LIKE to see. Here is what I get in the output of the query (assume 4 donors with multiple gift instances):
Account Name Address Gift Date Gift Amount Project Code Project Desc.
Donor A John Smith 111 Main 1/20/08 100.00 AAAAA Description A
Donor A John Smith 111 Main 2/25/08 100.00 AAAAA Description A
Donor A John Smith 111 Main 3/20/08 200.00 BBBBB Description B
Donor B James Jones 115 Mars 2/15/08 20.00 CCAAA Description CA
Donor B James Jones 115 Mars 4/20/08 20.00 CCAAA Description CA
Donor C Jin Chan 22 West 1/01/08 150.00 DDDDD Description D
Donor C Jin Chan 22 West 3/01/08 150.00 DDDDD Description D
Donor C Jin Chan 22 West 6/01/08 150.00 AAAAA Description A
Donor D Charles Smith 29 W Main 1/20/08 100.00 CCCCC Description C
The way the data is currently returned it is very hard to create a good mail merge file because I am working with thousands of records and as many as 25 lines per account. The account number, name, and address will be the same for each row for a donor, but gift amounts, dates, and projects may be different.
What I WANT it to do is to give me something like this (using donor A as an example) all on one row per unique account number.
Acc#t | Name | Address | Gift1 Date | Gift1 Amt | Gift1 Proj. | Gift1 ProjDesc | Gift2 Date |
Gift2 Amt | Gift2 Proj | Gift 2 Proj. Desc | Gift3 Date | Gift3 Amt | Gift3 Proj. | Gift3 ProjDesc...
My question is whether it is possible to do this in a SQL query. I've read some about crosstabs but they seem to be used more for totalling numbers, which isn't what I want to do.
Thank you for any help/guidance you can provide

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Maksymilian Mulawa Ten Centuries Points: 1336 More actions · Answer 1

Each account has a variable number of gift, which will make your resultset to return variable number of columns. I could suggest you following steps:

Logic should be inside stored procedure:

1. AccountGifts (the one you have provided) should have column AccountGiftSeqNo stroring values from 1...25, which could be generated when populating AccountGifts using RANK OVER (PARTITION BY Account ORDER BY ...)

1. create MailingList table with Account,Name, Address

2. Populate it with each Account details.

3. Calculate Max() number of Gifts for the account from AccountGifts table

4. Using combination of dynamic SQL and ALTER TABLE MailingList ADD COLUMN ...

generate required number of columns (4xmax gift count)

5. Build dynamically SQL to update

UPDATE ml

--dynamically generated code

SET ml.GiftDate1 = CASE WHEN ag.AccountGiftSeqNo = 1 THEN ag.GiftDate ELSE ml.GiftDate1 END

SET ml.GiftDate2 = CASE WHEN ag.AccountGiftSeqNo = 2 THEN ag.GiftDate ELSE ml.GiftDate2 END

--

FROM MailingList ml

INNER JOIN AccountGifts ag

ON ml.Account = ag.Account

I hope this helps.

I would advise to try to prepare mailing list on the SQL Server client side by building up Accounts collection with Gifts collection that will be populated using your original resultset.

Check how you could use Hashtable to make the whole process quick and efficient, I'm not sure if trying to do something like that in the database is just not an overkill.

Cheers, Max

Check-out free open-source utility Sql Server Replication Explorer[/url]

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 2

OR!... you can simply concatenate the values together in a nice long string up to VARCHAR(8000) in SQL Server 2000... see the following link for both the method and some pitfalls to avoid...

http://www.sqlservercentral.com/articles/Test+Data/61572/

That type of concatenation would be nearly perfect for a "mail merge" type of file. Of course, you can change the comma for a space or whatever... "Whatever" also includes CHAR(13) which is a carriage return... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)