On Wed, November 14, 2012 09:23, Mogens Melander wrote: > Guru's > > I've been tasked with creating a report with information about > which images are related to each active article. The images > are descriptive icons visualizing different properties. > > Articles can be active 'on' or not '', and only active is to > be included in the result. > > The number of available icons changes constantly, and are too > many, to use anything static. > > What I got is: > > Table: main > ----------------- > code (int) > active (on/off) > ----------------- > > Table: icon > ----------------- > id (int) > filename (char) > ----------------- > > Table: iconrel > ----------------- > code (int) > icon (int) > ----------------- > > What I need is something like: > > code filename1 filename2 filename3 filenameN > 1111 on off off on > > Hopefully somebody out there got more of a clue than I do. > > Any ideas, anybody? >
So, I got a little further with my problem. I found an article on: http://stackoverflow.com/questions/3122424/dynamic-mysql-query-view-for-crosstab Describing how to do the dynamic generation of SQL statements. That's all good, kind of. The resulting SQL looks like this: SELECT main.code , IF(iconstandardrel.icon = 4,1,0) AS 'internationalt_produkt.eps' , IF(iconstandardrel.icon = 3,1,0) AS 'god_vaerdi.eps' , IF(iconstandardrel.icon = 2,1,0) AS 'for_miljoeets_skyld.eps' , IF(iconstandardrel.icon = 1,1,0) AS 'ergonomisk_produkt.eps' , IF(iconstandardrel.icon = 6,1,0) AS 'saml_selv.eps' , IF(iconstandardrel.icon = 12,1,0) AS 'brandfarlig.eps' FROM iconstandardrel JOIN main ON main.code = iconstandardrel.code JOIN iconstandard ON iconstandard.id = iconstandardrel.icon ORDER BY iconstandardrel.code; Which produces results like: 101577, 1, 0, 0, 0, 0, 0 101679, 0, 1, 0, 0, 0, 0 101679, 1, 0, 0, 0, 0, 0 101681, 1, 0, 0, 0, 0, 0 101748, 0, 1, 0, 0, 0, 0 101748, 1, 0, 0, 0, 0, 0 But I would like to have One line per code: 101577, 1, 0, 0, 0, 0, 0 101679, 1, 1, 0, 0, 0, 0 101681, 1, 0, 0, 0, 0, 0 101748, 1, 1, 0, 0, 0, 0 Is it possible to achieve this in pure SQL ? -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
