CODEWITHSUNDEEP
sqlsql-servert-sql
Richard_2413
Richard_2413

Reputation: 109

SQL aggregrating within inserting statement

I am receiving files that look like this:

Order_ID     - Customer_ID     - Name

1            - 1               - Offer 1

1            - 1               - Offer 1

1            - 1               - Offer 2

1            - 1               - Offer 3

2            - 2               - Offer 4

2            - 2               - Offer 4

3            - 1               - Offer 4

Now i need to convert the data to look like this

Order_id - Customer_ID - Offer_1 - Offer_2 - Offer_ 3 - Offer_4

1 - 1 - 2 - 1 - 1 - 0 

2 - 2 - 0 - 0 - 0 - 2

3 - 1 - 0 - 0 - 0 - 1

Now i have it working with the following code :

INSERT INTO OutputFile
SELECT 
            [Unique id]       = INPUT.ORDER_ID,
            [klantnummer]     = INPUT.CUSTOMER_ID,
            Offer_1     = Offer_1.aantal,
            Offer_2             = Offer_2.aantal,
    Offer_3             = Offer_3.aantal,
    Offer_4             = Offer_4.aantal

FROM ( SELECT DISTINCT ORDER_ID, CUSTOMER_ID FROM INPUT_HOURLY )  AS INPUT
    LEFT JOIN (
        select ORDER_ID, CUSTOMER_ID, OFFER_ID, name, COUNT(name)  AS AANTAL  from 
            ( select ORDER_ID, CUSTOMER_ID, OFFER_ID, name from INPUT_HOURLY where offer_id ='1') TAB
         group by ORDER_ID, CUSTOMER_ID, OFFER_ID, name 
            ) AS offer_1 on offer_1.order_id = INPUT.order_id and offer_1.customer_id = INPUT.customer_id
    LEFT JOIN (
        select ORDER_ID, CUSTOMER_ID, OFFER_ID, name, COUNT(name)  AS AANTAL  from 
            ( select ORDER_ID, CUSTOMER_ID, OFFER_ID, name from INPUT_HOURLY where offer_id ='2') TAB
         group by ORDER_ID, CUSTOMER_ID, OFFER_ID, name 
            ) AS offer_2 on offer_2.order_id = INPUT.order_id and offer_2.customer_id = INPUT.customer_id
    LEFT JOIN (
        select ORDER_ID, CUSTOMER_ID, OFFER_ID, name, COUNT(name)  AS AANTAL  from 
            ( select ORDER_ID, CUSTOMER_ID, OFFER_ID, name from INPUT_HOURLY where offer_id ='3') TAB
         group by ORDER_ID, CUSTOMER_ID, OFFER_ID, name 
            ) AS offer_3 on offer_3.order_id = INPUT.order_id and offer_3.customer_id = INPUT.customer_id
    LEFT JOIN (
        select ORDER_ID, CUSTOMER_ID, OFFER_ID, name, COUNT(name)  AS AANTAL  from 
            ( select ORDER_ID, CUSTOMER_ID, OFFER_ID, name from INPUT_HOURLY where offer_id ='4') TAB
         group by ORDER_ID, CUSTOMER_ID, OFFER_ID, name 
            ) AS offer_4 on offer_4.order_id = INPUT.order_id and offer_4.customer_id = INPUT.customer_id

Now my question is: is there a better and easier way to do this? Since in the real file there are over 25 different offers i need to fill and if i do it like this it will become a huge query.

Upvotes: 2

Views: 69

Answers (2)

CoderDonna
CoderDonna

Reputation: 13

You can use PIVOT to make rows into columns. If you want/need to get around the aggregation requirement with PIVOT, take a look at this article: http://sqlmag.com/t-sql/pivoting-without-aggregation

Otherwise, just take a look at the PIVOT function via Google. Many good articles out there to cover every facet.

Donna

Upvotes: 0

sagi
sagi

Reputation: 40471

You should use conditional aggregation with CASE EXPRESSION instead of all this unnecessary LEFT JOINs :

SELECT ORDER_ID, CUSTOMER_ID,
       COUNT(CASE WHEN offer_id = '1' THEN 1 END) as offer_1,
       COUNT(CASE WHEN offer_id = '2' THEN 1 END) as offer_2,
       COUNT(CASE WHEN offer_id = '3' THEN 1 END) as offer_3,
       COUNT(CASE WHEN offer_id = '4' THEN 1 END) as offer_4
FROM INPUT_HOURLY 
GROUP BY ORDER_ID, CUSTOMER_ID

COUNT() ignore NULL values, so, when the condition inside the CASE is false, the default value(because there is no ELSE) will be NULL , so it will count 1 only when it answer the condition.

Upvotes: 2

Related Questions