Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Pivot Table test
#MySQL 5.7.12 #please drop objects you've created at the end of the script #or check for their existance before creating #'\\' is a delimiter create table assets ( id int, status_id int ); create table statuses ( id int, name varchar(10) ); INSERT INTO assets(id, status_id) VALUES (1, 1), (2, 1), (3,1), (2,2) , (3,2); INSERT INTO statuses(id, name) VALUES (1, "test1"), (2, "test2"); set @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'count(case when a.status_id = ''', id, ''' then 1 end) AS ', replace(name, ' ', '') ) ) INTO @sql from statuses; SET @sql = CONCAT('select s.name,', @sql,' from statuses s LEFT JOIN assets a ON s.id = a.status_id Group by s.name'); PREPARE stmt from @sql; EXECUTE stmt; DEALLOCATE Prepare stmt; drop table assets; drop table statuses;
run
|
edit
|
history
|
help
0
ITM_14210069
par
new_road
test
MySQL Sandbox: Superheroes
Show Tables
busID
gandu
school
list with indented categories