How do I pivot values into columns with SQLite?

Question

I made a table called tbl with this code:

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:

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

What query must I run to get what I want?

Vamsi Prabhala · Accepted Answer · 2016-05-11 18:57:53Z

5

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

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

answered May 11, 2016 at 18:57

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dresden Over a year ago

sqlfiddle.com/#!5/763ad/3 If they want to mess around with it anymore.

Username Over a year ago

If I add more rows with more different Genres, is there a way to take those into account without having to add more count(distinct case when Genre` = 'new genre' then 1 end) as newgenre` statements?

Vamsi Prabhala Over a year ago

No,unfortunately afaik. You will have to do this manually. If it were SQL Server or Oracle you would have had an option of doing a dynamic pivot.

Username Over a year ago

@vkp How about MySQL or PostgreSQL?

Jeremy Field · Accepted Answer · 2020-05-25 10:04:19Z

1

The accepted answer worked for me.

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

see also:

Combining the results of two SQL queries as separate columns

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

edited May 25, 2020 at 10:04

Jeremy Field

6997 silver badges13 bronze badges

answered Sep 12, 2018 at 18:05

Peruz

4334 silver badges11 bronze badges

Collectives™ on Stack Overflow

How do I pivot values into columns with SQLite?

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related