Retrieve different number of rows for each category from SQL Server table

Question

I need to generate a list of addresses to which my client is going to send merchandise catalogs. They want to send a certain number of catalogs (X) to a particular zip code (Y) [This has to do with average income in those zip codes]

I have 2 tables in SQL Server 2008 R2: CatalogRequests and AddressList

CatalogRequests is a list of how many catalogs we should send to each zip code in our customer database:

|  Zip  |  QuantityRequested  |
-------------------------------
| 12345 |         150         |
| 13445 |         800         |
| 45678 |         200         |
| 41871 |         350         |
| 77777 |         125         |

AddressList is just that, a list of addresses =)

|  Name  |     Address1    |  Address2  |      City     |  State  |  Zip  |
---------------------------------------------------------------------------
|  Bruce | 1 BatCave Dr    |            |  Gotham City  |   IL    | 12345 |
|  Clark | 9 Smallville St |   Apt A    |  Metropolis   |   NY    | 45678 |
|  Thor  | 5 Valhalla Way  |            |    Asgard     |   ??    | 77777 |

I racked my brain for a little while trying to do this with an SQL query, then gave up and wrote a small C# program to do what I needed to do (basically, generating a bunch of SQL queries - one for each record in CatalogRequests).

My question is, how could I have done this with one SQL query? I'm just curious at this point, it seems like there is a way to do it and I'm just missing something. Or might not be possible and I'm crazy =)

The result set would be records from AddressList that met the requirements in CatalogRequest (for example, 150 records with a zip code of 12345, 800 records with a zip code of 13445, etc).

Answer 1

Hmm... What about something like this:

;with addressListWithID 
AS 
(
    SELECT name, address1, adress2, city, state, zip, 
           ROW_NUMBER() OVER(partition by zip   order by newid()) as Row
    FROM AddressList 
)
SELECT A.name, A.address1, A.adress2, A.city, A.state, A.zip
FROM addressListWithID A 
    INNER JOIN CatalogRequests C
        ON C.zip = A.zip 
            AND A.row <= C.QuantityRequested 

Random addresses in quantity requested.

Answer 2

Untested, because there is no DDL and no test data:

SELECT  A.name ,
        A.address1 ,
        A.adress2 ,
        A.city ,
        A.[state] ,
        A.zip
FROM    CatalogRequests AS C
        CROSS APPLY ( SELECT TOP ( C.QuantityRequested )
                                A.name ,
                                A.address1 ,
                                A.adress2 ,
                                A.city ,
                                A.[state] ,
                                A.zip
                      FROM      addressListWithID AS A
                      WHERE     C.zip = A.zip
                    ) AS A

Answer 3

One method is to use dynamic SQL (which is dangerous and not to be taken lightly):

DECLARE @SQL varchar(max)

SET @SQL = 'USE Database'

SELECT @SQL = @SQL + 'SELECT TOP ' 
            + QuantityRequired
            + ' * FROM AddressList WHERE Zip = '
            + Zip
            + ' UNION ALL'
FROM CatalogRequests

SET @SQL = LEFT(@SQL, (LEN(@SQL - 10))) -- remove last UNION ALL 

PRINT (@SQL)

-- EXEC (@SQL)

Check the link for the canonical reference on Dynamic SQL.