How to aggregate data across multiple columns?

Question

I have a table containing raw data that looks like so:

geoid  business
-------------------
1      BestBuy
1      WalMart
1      Target
2      BestBuy
2      Target
2      BestBuy
3      WalMart

These businesses map to columns in another table like so:

BestBuy -> BigBox_1
Walmart -> BigBox_2
Target  -> BigBox_3

I'm trying to find a clean way to query the raw data table and populate the destination table which should represent the same data like so:

geoid BigBox_1_Count  BigBox_2_Count   BigBox_3_Count
----------------------------------------------------------
1    |            1  |              1 |                1
2    |            2  |              0 |                1
3    |            0  |              0 |                1

I realize that I can do this with three different queries like so:

INSERT INTO destTable (geoid, BigBox_1_Count)
SELECT geoid, COUNT(*) WHERE business = 'BestBuy'

and then update statement for the other two.

But I'm wondering if there is a way that I could insert all of the counts to the appropriate column with a single insert statement? Can this be done?

Note - I realize this table structure is not ideal. I didn't create it and redesigning it is on my list of things to do but for now I need to work with what I have.

Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Clockwork-Muse, Nov 12 '18 at 19:02

score 2 · Accepted Answer · answered Nov 12 '18 at 17:53

2

I would do aggregation :

INSERT INTO destTable (geoid, BigBox_1_Count, . . . )
     SELECT geoid, 
            SUM(CASE WHEN business = 'BestBuy' THEN 1 ELSE 0 END) AS BigBox_1_Count,
            SUM(CASE WHEN business = 'WalMart' THEN 1 ELSE 0 END) AS BigBox_2_Count,
            SUM(CASE WHEN business = 'Target' THEN 1 ELSE 0 END) AS BigBox_3_Count
     FROM table t
     GROUP BY geoid;

answered Nov 12 '18 at 17:53

Yogesh Sharma

49,870
5
26
52

Do you know if it's possible to do the equivalent of a `like` query on the business name? So that I could do something like `business like 'Bestbuy%'`? – Abe Miessler Nov 12 '18 at 18:03
@AbeMiessler. . . Yes you can use `LIKE` in `case` expression. – Yogesh Sharma Nov 12 '18 at 18:04

How to aggregate data across multiple columns?

1 Answers1