Clever Geek Handbook

SQL Column / Row Grouping

I am new to SQL. I am looking for a simple SQL solution to concatenate a row / column for a row whose column contains the same data, in this case the zip code. For example, viewing the data looks like this:

state, county, city, zip, count

"CA", "ALAMEDA", "HAYWARD", "94541", 5371
"CA", "ALAMEDA", "HAYWARD", "94542", 2209
"CA", "ALAMEDA", "HAYWARD", "94544", 7179
"CA", "ALAMEDA", "HAYWARD", "94545", 4209
"CA", "ALAMEDA", "CASTRO VALLEY", "94546", 7213
"CA", "ALAMEDA", "HAYWARD", "94546", 37
"CA", "ALAMEDA", "LIVERMORE", "94550", 9809
"CA", "ALAMEDA", "LIVERMORE", "94551", 6558
"CA", "ALAMEDA", "CASTRO VALLEY", "94552", 3121
"CA", "ALAMEDA", "HAYWARD", "94552", 12
"CA", "ALAMEDA", "FREMONT", "94555", 5392

I would like to get the data so that it looks like this:

state, county, city, zip, count

"CA", "ALAMEDA", "HAYWARD", "94541", 5371
"CA", "ALAMEDA", "HAYWARD", "94542", 2209
"CA", "ALAMEDA", "HAYWARD", "94544", 7179
"CA", "ALAMEDA", "HAYWARD", "94545", 4209
"CA", "ALAMEDA", "CASTRO VALLEY / HAYWARD", "94546", 7250
"CA", "ALAMEDA", "LIVERMORE", "94550", 9809
"CA", "ALAMEDA", "LIVERMORE", "94551", 6558
"CA", "ALAMEDA", "CASTRO VALLEY HAYWARD", "94552", 3133
"CA", "ALAMEDA", "FREMONT", "94555", 5392

You can see that the data has been combined or summed in the two rows. For rows containing the same postal code, the city names (both) appear in the city column, and count is the sum of the count from each row.

Is there a way to do this using SQL? Even if it requires two different SQL statements.

+3


source to share


2 answers


Assuming SQL Server you can use FOR XML

to get the results you want .

select distinct t.state,t.county,t.zip,t2.sumcount,
STUFF(
        (
            SELECT '/' + city AS [text()]
            FROM mytable t3
            WHERE t.zip = t3.zip
            FOR XML PATH('')
        ), 1, 1, '') AS ColList
from mytable t 
  join (select zip, sum(count) as sumcount
       from mytable
       group by zip) t2 on t.zip=t2.zip

And some SQL Fiddle .

If you are using MySQL take a look at GROUP_CONCAT

:



select distinct t.state,t.county,t.zip,t2.sumcount,
GROUP_CONCAT(t.city) as cities
from mytable t 
  join (select zip, sum(count) as sumcount
       from mytable
       group by zip) t2 on t.zip=t2.zip
GROUP BY t.state,t.county,t.zip,t2.sumcount

And also Fiddle .

Good luck.

+2


source


I improved on sgeddes' excellent answer to actually get the counter and avoid duplicate entries, and added script support so you can test it. This assumes SQL Server.

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MYTABLE') BEGIN
  drop table MYTABLE;
END;
go

create table MYTABLE
(
  state nvarchar(2)
 ,county nvarchar(100)
 ,city nvarchar(100)
 ,zip nvarchar(10)
)
go

insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','HAYWARD','94541');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','HAYWARD','94541');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','HAYWARD','94544');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','HAYWARD','94545');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','CASTRO VALLEY','94546');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','CASTRO VALLEY','94546');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','CASTRO VALLEY','94546');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','CASTRO VALLEY','94546');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','CASTRO VALLEY','94546');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','HAYWARD','94546');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','HAYWARD','94546');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','HAYWARD','94546');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','HAYWARD','94546');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','LIVERMORE','94550');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','LIVERMORE','94551');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','CASTRO VALLEY','94552');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','HAYWARD','94552');
insert into MYTABLE(state,county,city,zip) values('CA','ALAMEDA','FREMONT','94555');

select distinct
  t.state
 ,t.county
 ,t.zip
 ,t2.sumcount
 ,STUFF((
       SELECT distinct '/' + city AS [text()]
       FROM mytable t3
       WHERE t.zip = t3.zip
       FOR XML PATH('')
       ), 1, 1, '') AS ColList
from
  mytable t 
  inner join
  (
  select zip, sum(count) as sumcount
  from
    (
    select zip,count(*) as count
    from mytable
    group by zip
    ) x
  group by zip
  ) t2
  on t.zip=t2.zip


The result looks like this:

state   county  zip sumcount    ColList
CA  ALAMEDA 94541   2   HAYWARD
CA  ALAMEDA 94544   1   HAYWARD
CA  ALAMEDA 94545   1   HAYWARD
CA  ALAMEDA 94546   9   CASTRO VALLEY/HAYWARD
CA  ALAMEDA 94550   1   LIVERMORE
CA  ALAMEDA 94551   1   LIVERMORE
CA  ALAMEDA 94552   2   CASTRO VALLEY/HAYWARD
CA  ALAMEDA 94555   1   FREMONT
0


source






More articles:


All Articles