Get a different number of rows for each category from a SQL Server table

Question

I need to create a list of addresses to which my client will send product catalogs. They want to send a certain number of directories (X) to a specific zip code (Y) [This is due to the average income in these zip codes]

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

CatalogRequests is a list of how many directories 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 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 got a little tired of my brain 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 by creating a bunch of SQL queries - one for each record in CatalogRequests ).

My question is: how could I do this with a single SQL query? I'm just curious, at the moment it seems that there is a way to do this, and I just miss something. Or it may be impossible, and I'm crazy =)

The AddressList set will be records from AddressList that satisfy the requirements in CatalogRequest (for example, 150 records with a postal code of 12345, 800 records with a postal code of 13445, etc.).

Answer 1

Hmm ... How 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 the requested quantity.

Answer 2

Not indexed 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 should not be taken easily) :

 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 out the link to the canonical link to Dynamic SQL.