How do I pivot values into columns with SQLite?

I made a table called tbl with this code:

tbl

CREATE TABLE tbl
(
`Year` int,
`Album` varchar(255),
`Artist` varchar(255),
`Label` varchar(255),
`Genre` varchar(255),
`id` int
)
;

INSERT INTO tbl
(
`Year`,
`Album`,
`Artist`,
`Label`,
`Genre`,
`id`
)
VALUES
(1990, "Greatest Hits", "The Best", "Least Def", "hip hop", 123),
(1990, "Greatest Hits", "The Best", "Roofless", "hip hop", 123),
(1990, "4-Boyz", "3 Guyz", "Pacific", "pop-dance", 23),
(1990, "4-Boyz", "3 Guyz", "Atlantic", "pop-dance", 23)
;


I want to run a query to show me the count of genres for each year, without double counting because of the Label column. I want this:

Label

Year, hip hop, pop-dance
1990, 1, 1


What query must I run to get what I want?

2 Answers
2

Because you can't use pivot, you can do this.

pivot

select year,
count(distinct case when `Genre` = 'hip hop' then 1 end) as hiphop,
count(distinct case when `Genre` = 'pop-dance' then 1 end) as popdance
from tbl
group by year


count(distinct case when

The accepted answer worked for me.

I add here a more complex case, with join, in case someone needs.

see also:

Combining 2 SQL queries and getting result set in one

my example:

select Info.*, Roots.*, ColeColeFit.*, f10.*, f20k.* from Info join (select * from Data where Frequency = 10) as f10 on f10.Info_ID = Info.id join (select * from Data where Frequency = 20000) as f20k on f20k.Info_ID = Info.id join Roots on Info.File_Num = Roots."Plant number" join ColeColeFit on ColeColeFit.id = Info.id


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.