Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Query to pivot data

Posted on 2014-02-22
5
Medium Priority
?
359 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
  • 3
5 Comments
 
LVL 43

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 43

Accepted Solution

by:
pcelba earned 2000 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 43

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

963 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