I have distinct data that I want to pivot/cross, for instance
Given table A with
name tag
Bob sport
Bob action
Bob comedy
Tom action
Tom drama
Sue sport
I'd like a query that transforms the data to
name sport action comedy drama
Bob 1 1 1 0
Tom 0 1 0 1
Sue 1 0 0 0
For any number n of distinct tags.
How would I create this transformation using sql if I didn't know the distinct tags before I begin.
Some simple solutions adequate for some cases. Using this table (SQL Fiddle is not working right now)
create table a (
name text,
tag text
);
insert into a (name, tag) values
('Bob', 'sport'),
('Bob', 'action'),
('Bob', 'comedy'),
('Tom', 'action'),
('Tom', 'drama'),
('Sue', 'sport');
A simple arrays aggregation if they can be split somewhere else
select
name,
array_agg(tag order by tag) as tags,
array_agg(total order by tag) as totals
from (
select name, tag, count(a.name) as total
from
a
right join (
(select distinct tag from a) t
cross join
(select distinct name from a) n
) c using (name, tag)
group by name, tag
) s
group by name
order by 1
;
name | tags | totals
------+-----------------------------+-----------
Bob | {action,comedy,drama,sport} | {1,1,0,1}
Sue | {action,comedy,drama,sport} | {0,0,0,1}
Tom | {action,comedy,drama,sport} | {1,0,1,0}
For JSON aware clients a set of JSON objects
select format(
'{%s:{%s}}',
to_json(name),
string_agg(o, ',')
)::json as o
from (
select name,
format(
'%s:%s',
to_json(tag),
to_json(count(a.name))
) as o
from
a
right join (
(select distinct tag from a) t
cross join
(select distinct name from a) n
) c using (name, tag)
group by name, tag
) s
group by name
;
o
-----------------------------------------------------
{"Bob":{"action":1,"comedy":1,"drama":0,"sport":1}}
{"Sue":{"action":0,"comedy":0,"drama":0,"sport":1}}
{"Tom":{"action":1,"comedy":0,"drama":1,"sport":0}}
or a single JSON object
select format('{%s}', string_agg(o, ','))::json as o
from (
select format(
'%s:{%s}',
to_json(name),
string_agg(o, ',')
) as o
from (
select name,
format(
'%s:%s',
to_json(tag),
to_json(count(a.name))
) as o
from
a
right join (
(select distinct tag from a) t
cross join
(select distinct name from a) n
) c using (name, tag)
group by name, tag
) s
group by name
) s
;
o
---------------------------------------------------------------------------------------------------------------------------------------------------------
{"Bob":{"action":1,"comedy":1,"drama":0,"sport":1},"Sue":{"action":0,"comedy":0,"drama":0,"sport":1},"Tom":{"action":1,"comedy":0,"drama":1,"sport":0}}
