Run script multiple times using variable in constraint

Question

table

Is there a way of populating the Cust1, Cust2, Cust3 columns in Table2 that doesn't involve running the same query with several different "WHERE" clauses?

For example, right now I would resort to doing this:

INSERT INTO Table2(Cust1_Totals)
SELECT Fruit, SUM(quantity)
FROM Table1
WHERE Customer_ID LIKE 1
GROUP BY Fruit

INSERT INTO Table2(Cust2_Totals)
SELECT Fruit, SUM(quantity)
FROM Table1
WHERE Customer_ID LIKE 2
GROUP BY Fruit

INSERT INTO Table2(Cust3_Totals)
SELECT Fruit, SUM(quantity)
FROM Table1
WHERE Customer_ID LIKE 3
GROUP BY Fruit

Ideally, I would like to have another table with a Customer_ID column of all the unique customers and then I would reference each one in a loop in the above query. SQL Server 2008.

Andomar · Accepted Answer · 2012-02-07 21:45:39Z

4

You could use case to generate sums per customer in separate columns:

INSERT  Table2
        (Produce, Cust1_Totals, Cust2_Totals, Cust3_Totals)
SELECT  Fruit
,       SUM(case when Customer_ID = 1 then quantity end)
,       SUM(case when Customer_ID = 2 then quantity end)
,       SUM(case when Customer_ID = 3 then quantity end)
FROM    Table1
WHERE   Customer_ID IN (1,2,3)
GROUP BY 
        Fruit

Generating a variable numbers of columns would require dynamic sql. It's usually easier to let Excel that. In Excel-speak, it's known as "pivoting".

answered Feb 7, 2012 at 21:45

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

eek Over a year ago

This seems like a good idea, but I am worried about instances where I will have about 10 sets of columns per "customer" to populate. This would lead to a lot of lengthy statements. Would things be different if I had a fixed # of customers in another table? I don't really need this to be dynamic. I just want it to run for each customer in the other table.

Las Ten · Accepted Answer · 2012-02-07 22:21:25Z

0

Maybe something like:

select * from table1
pivot (sum(quantity) for customerid in ([1], [2], [3])) as pivotTable

But you still have to specify the customer IDs here.

answered Feb 7, 2012 at 22:21

Las Ten

1,17510 silver badges22 bronze badges

Collectives™ on Stack Overflow

Run script multiple times using variable in constraint

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related