Can the operation shown in the following image be achieved elegantly in pure SQL and without hardcoding names into the statement?
Tables A, B and C are given. The one on the right is the result I would like to get.
The following statement
SELECT name, val_name, val
FROM A, B, C
WHERE a_id = A.id
and b_id = B.id
returns the correct information but not in the form I would like:
foo|goos|6
foo|blobs|7
bar|goos|8
baz|blobs|9
You can do it using conditional aggregates:
SELECT A.NAME,
SUM(CASE WHEN B.VAL_NAME = 'GOOS' THEN C.VAL END) AS GOOS,
SUM(CASE WHEN B.VAL_NAME = 'BLOBS' THEN C.VAL END) AS BLOBS
FROM A
LEFT JOIN C ON A.ID = C.A_ID
LEFT JOIN B ON C.B_ID = B.ID
GROUP BY A.NAME
Demo above is in MySQL. It should also work in most RDBMSs as this is ANSI standard SQL.
The solution clearly assumes that VAL_NAME values are already known by the time you write the query.
Prep the sample data:
create table a (
id number,
name varchar2(10)
)
/
create table b (
id number,
val_name varchar2(10)
)
/
create table c (
id number,
a_id number,
b_id number,
val number
)
/
insert into a values ( 1, 'FOO' );
insert into a values ( 2, 'BAR' );
insert into a values ( 3, 'BAZ' );
insert into a values ( 4, 'QUX' );
insert into b values ( 1, 'GOOS' );
insert into b values ( 2, 'BLOBS' );
insert into c values ( 1, 1, 1, 6 );
insert into c values ( 2, 1, 2, 7 );
insert into c values ( 3, 2, 1, 8 );
insert into c values ( 4, 3, 2, 9 );
commit;
Try this query:
select distinct a.name,
first_value(case when b_goo.val_name is not null then c.val else null end) over (partition by a.name order by b_goo.val_name nulls last ) goos,
first_value(case when b_blob.val_name is not null then c.val else null end) over (partition by a.name order by b_blob.val_name nulls last ) blobs
from a
left outer join c
on c.a_id = a.id
left outer join b b_goo
on b_goo.id = c.b_id
and b_goo.val_name = 'GOOS'
left outer join b b_blob
on b_blob.id = c.b_id
and b_blob.val_name = 'BLOBS'
/
Results:
NAME GOOS BLOBS
---------- ---------- ----------
QUX
BAR 8
FOO 6 7
BAZ 9
[edit] sorry, you wanted to show the ids, not the names, fixed that here[/edit]
So I just do some outer joins first, using 2 "copies" of your table B .. so I can have a GOO in 1 column and BLOBs in another. Then I use FIRST_VALUE analytic function to pick out the first non-null value which helps collapse the duplicate rows .. DISTINCT helps with that as well.
I did this in Oracle, however, it should work as is in other RDBMS.
Speaking solely of MS SQL Server, the answer (to the question of "can it be done elegantly") is No.
After joining the three tables, you would have to pivot the contents of B.VAL_NAME. This can only be done (in T-SQL) if you know the names of the values being "pivoted out" beforehand (which I am assuming you do not--otherwise, you wouldn't be storing them as explicit values in table B). As you do not know the names of the columns until run time, you would have to build the query at run time, using dynamic SQL. It works, but all told this kind of solution--of which I have written far too many instances of--is anything but "elegant".
If the resulting column names are known beforehand, @Dittos' solution will work, and you also wouldn't have to mess with inelegant pivot statements.
Try
select
a.name,
sum(case when d.valname = 'goods' then d.val
else 0 end) as goods,
sum(case when d.valname = 'blobs' then d.val
else 0 end) as blobs
from a
left join (
select c.aid, c.val, b.valname
from c
join b
on c.bid = b.id
) d
on d.aid = a.id
group by a.name
