Don't I have to guess at what the max number of subjects would be then? and then won't I have a bunch of BRs all over the place if there is only one subject?
On Tue, Apr 14, 2015 at 2:02 PM, Nick Clemens <n...@quecheelibrary.org> wrote: > You can also use the position marker in the xpath to pick an arbitrary > number of subject headings: > > GROUP_CONCAT(DISTINCT > ExtractValue(m.marcxml,'//datafield[@tag="650"][position()=1]/subfield[@code="a"]'),' > BR > ',ExtractValue(m.marcxml,'//datafield[@tag="650"][position()=2]/subfield[@code="a"]'),' > BR > ',ExtractValue(m.marcxml,'//datafield[@tag="650"][position()=3]/subfield[@code="a"]') > SEPARATOR ' BR ') AS 'Subject' > > On Tue, Apr 14, 2015 at 1:17 PM, Nick Clemens <n...@quecheelibrary.org> > wrote: > >> I think the problem is that return from ExtractValue returns only one item >> so the GROUP_CONCAT is only seeing one thing come in and doesn't add the >> separator: >> >> See this: >> >> http://stackoverflow.com/questions/10808149/mysql-use-extractvaluexml-value-values-to-get-all-multiple-values-split-on >> >> On Tue, Apr 14, 2015 at 11:05 AM, Nicole Engard <neng...@gmail.com> wrote: >> >>> Hi all, >>> >>> I'm working on this report and I want there to be a BR between each >>> distinct 650a - but for some reason it's not working. I worked on this >>> in channel for a while and you can see the log here: >>> http://irc.koha-community.org/koha/2015-04-14#i_1663170 >>> >>> Here's the report: >>> >>> >>> Select b.biblionumber, GROUP_CONCAT(DISTINCT ExtractValue(m.marcxml, >>> '//datafield[@tag="650"]/subfield[@code="a"]') SEPARATOR ' BR ') AS >>> 'Subject',i.itype AS 'IType' >>> FROM biblio b >>> LEFT JOIN biblioitems m using (biblionumber) >>> left join items i using (biblioitemnumber) >>> WHERE i.location in >>> >>> ('ARCHIVES','ARCHSIZE','HISTORYMED','HISTMEDOVZ','HISTMEDREF','RAREBKROOM','RAREOVRSIZ') >>> and i.itype != 'JOURNAL' and ExtractValue(m.marcxml, >>> '//datafield[@tag="650"]/subfield[@code="a"]') is not null and >>> ExtractValue(m.marcxml, '//datafield[@tag="650"]/subfield[@code="a"]') >>> != '' >>> group by i.biblionumber >>> ORDER BY b.biblionumber >>> >>> >>> >>> Thanks in advance! >>> Nicole >>> _______________________________________________ >>> Koha mailing list http://koha-community.org >>> Koha@lists.katipo.co.nz >>> http://lists.katipo.co.nz/mailman/listinfo/koha >>> >> >> >> >> -- >> Nick Clemens >> Quechee & Wilder Libraries >> n...@quecheelibrary.org >> http://www.QuecheeLibrary.org >> Q (802) 295-1232 W (802) 295-6341 >> > > > > -- > Nick Clemens > Quechee & Wilder Libraries > n...@quecheelibrary.org > http://www.QuecheeLibrary.org > Q (802) 295-1232 W (802) 295-6341 > _______________________________________________ > Koha mailing list http://koha-community.org > Koha@lists.katipo.co.nz > http://lists.katipo.co.nz/mailman/listinfo/koha _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha