Solved

Query to pivot data

Posted on 2014-02-22
5
347 Views
Last Modified: 2014-02-24
I have created a query that reads data from several tables and each record is contained on a single line.  I did this joining several queries and the result was a very long query (not time, but characters).  Please see below the query I used:

select *
from
(
	select id from aaa
) aaa
left join
(
	select aaa_id,bbb.id as bbb1_id,
	count(case when id is not null then 1 else null end) as bbb1_count
	from bbb
	where bbb.v=1
	group by aaa_id,id
) bbb1
on aaa.id=bbb1.aaa_id
left join
(
	select aaa_id,bbb.id as bbb2_id,
	count(case when id is not null then 1 else null end) as bbb2_count
	from bbb
	where bbb.v=2
	group by aaa_id,id
) bbb2
on aaa.id=bbb2.aaa_id
left join
(
	select aaa_id,bbb_id,ccc.id as ccc1_id,
	count(case when ccc.id is not null then 1 else null end) as ccc11_count
	from bbb
	inner join ccc on bbb.id=ccc.bbb_id
	where bbb.v=1
	  and ccc.v=1
	group by aaa_id,bbb_id,ccc.id
) ccc11
on aaa.id=bbb1.aaa_id and bbb1.bbb1_id=ccc11.bbb_id
left join
(
	select aaa_id,bbb_id,ccc.id as ccc2_id,
	count(case when ccc.id is not null then 1 else null end) as ccc12_count
	from bbb
	inner join ccc on bbb.id=ccc.bbb_id
	where bbb.v=1
	  and ccc.v=2
	group by aaa_id,bbb_id,ccc.id
) ccc12
on aaa.id=bbb1.aaa_id and bbb1.bbb1_id=ccc12.bbb_id
left join
(
	select aaa_id,bbb_id,ccc.id as ccc21_id,
	count(case when ccc.id is not null then 1 else null end) as ccc21_count
	from bbb
	inner join ccc on bbb.id=ccc.bbb_id
	where bbb.v=2
	  and ccc.v=1
	group by aaa_id,bbb_id,ccc.id
) ccc21
on aaa.id=bbb2.aaa_id and bbb2.bbb2_id=ccc21.bbb_id
left join
(
	select aaa_id,bbb_id,ccc.id as ccc22_id,
	count(case when ccc.id is not null then 1 else null end) as ccc22_count
	from bbb
	inner join ccc on bbb.id=ccc.bbb_id
	where bbb.v=2
	  and ccc.v=2
	group by aaa_id,bbb_id,ccc.id
) ccc22
on aaa.id=bbb2.aaa_id and bbb2.bbb2_id=ccc22.bbb_id

Open in new window


Obviously, this is a very long query and I would like to reduce it to a  simple, elegant query.  I've tried this:

select *
from aaa
left join bbb on aaa.id=bbb.aaa_id
left join ccc on bbb.id=ccc.bbb_id

Open in new window


But end up with multiple lines for each record (record would be aaa.id).  Is there a better way to accomplish the result from my first query but much shorter?  I'm using postgres 8.4 so I'm not sure what sort of pivot options are available but I definitely won't be able to perform mssql or mysql queries that have those specific functions.

I'm including some data to generate tables with data:
create table aaa (id integer);
create table bbb (id integer,aaa_id integer, v integer);
create table ccc (id integer,bbb_id integer, v integer);
insert into aaa(id) values(1);
insert into aaa(id) values(2);
insert into aaa(id) values(3);
insert into aaa(id) values(4);
insert into bbb(id,aaa_id,v) values(11,1,1);
insert into bbb(id,aaa_id,v) values(12,1,2);
insert into bbb(id,aaa_id,v) values(21,2,1);
insert into bbb(id,aaa_id,v) values(22,2,2);
insert into bbb(id,aaa_id,v) values(31,3,1);
insert into bbb(id,aaa_id,v) values(42,4,2);
insert into ccc(id,bbb_id,v) values(111,11,1);
insert into ccc(id,bbb_id,v) values(112,11,2);
insert into ccc(id,bbb_id,v) values(211,22,1);
insert into ccc(id,bbb_id,v) values(312,31,2);
insert into ccc(id,bbb_id,v) values(411,42,1);
insert into ccc(id,bbb_id,v) values(412,42,2);

Open in new window

0
Comment
Question by:mock5c
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 42

Expert Comment

by:pcelba
ID: 39880705
You may try this:
SELECT aaa.id, 
       COUNT(case when bbb.v=1 THEN bbb.id ELSE null END) as bbb1_count, 
       COUNT(case when bbb.v=2 THEN bbb.id ELSE null END) as bbb2_count, 
       COUNT(case when bbb.v=1 and ccc.v=1 THEN ccc.id ELSE null END) as ccc11_count, 
       COUNT(case when bbb.v=1 and ccc.v=2 THEN ccc.id ELSE null END) as ccc12_count, 
       COUNT(case when bbb.v=2 and ccc.v=1 THEN ccc.id ELSE null END) as ccc21_count, 
       COUNT(case when bbb.v=2 and ccc.v=2 THEN ccc.id ELSE null END) as ccc22_count
  FROM aaa
  LEFT JOIN bbb ON aaa.id=bbb.aaa_id
  LEFT JOIN ccc ON bbb.id=ccc.bbb_id
 GROUP BY aaa.id

Open in new window

There is a difference between your and my query so we should explain why bbb1_count = 1 on the first result row and why bbb2_count = 1 on the last result row in your case.
0
 
LVL 42

Accepted Solution

by:
pcelba earned 500 total points
ID: 39880715
We have to count distinct values in the case on table bbb because several ccc rows related to one bbb row multiplies the result...
SELECT aaa.id, 
       COUNT(DISTINCT case when bbb.v=1 THEN bbb.id ELSE null END) as bbb1_count, 
       COUNT(DISTINCT case when bbb.v=2 THEN bbb.id ELSE null END) as bbb2_count, 
       COUNT(case when bbb.v=1 and ccc.v=1 THEN ccc.id ELSE null END) as ccc11_count, 
       COUNT(case when bbb.v=1 and ccc.v=2 THEN ccc.id ELSE null END) as ccc12_count, 
       COUNT(case when bbb.v=2 and ccc.v=1 THEN ccc.id ELSE null END) as ccc21_count, 
       COUNT(case when bbb.v=2 and ccc.v=2 THEN ccc.id ELSE null END) as ccc22_count
  FROM aaa
  LEFT JOIN bbb ON aaa.id=bbb.aaa_id
  LEFT JOIN ccc ON bbb.id=ccc.bbb_id
 GROUP BY aaa.id

Open in new window

It is better visible when you issue ungroupped query:
SELECT * FROM aaa
  LEFT JOIN bbb ON aaa.id=bbb.aaa_id
  LEFT JOIN ccc ON bbb.id=ccc.bbb_id

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 39882997
To get one row for each aaa.id will require some type of aggregation of values from the bbb and ccc tables, something like the embedded snippet. Is that what you want?
aaa.id	bbb.id	bbb.aaa_id	bbb.v	ccc.id		ccc.bbb_id	ccc.v
1	11,12	1		1,2	111,112		11		1,2
2	21,22	2		1,2	211		22		1
3	31	3		1	312		31		2
4	42	4		2	411,412		42		1,2

Open in new window

0
 
LVL 42

Expert Comment

by:pcelba
ID: 39883082
@mock5c: Do you need IDs on output? I would guess no except aaa.id
0
 

Author Closing Comment

by:mock5c
ID: 39883123
This is exactly what I needed.  It's a condensed query that gives me the exact same result of the very long query I posted.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question