CoryDunne.com

Aggregating ISBNs From Horizon

Jan 20, 2011 Code, Library

I’m working on creating a spreadsheet that will be imported into another application. The format for the spreadsheet is fairly picky, as it expects to match titles, authors, ISBNs, etc. The problem is that it wants each bib to be a new line.

Sounds simple, right? Well, when you look beneath the hood (this is a Horizon ILS using a SQL Server backend), each ISBN (and author, and UPC, etc) is a separate line in the database. This is great from a normalization standpoint, but despite my many years of writing SQL queries for SQL Server, this never ceases to amaze me how much of a pain in the butt it can be.

MySQL has an awesome GROUP_CONCAT function that handles this perfectly. MSSQL does not.

Here’s a bit of code I used to make this work. You can easily modify it to pull off of any MARC field. My actual implementation uses the 592 note field to look for a tag that flags the title as being an upcoming bestseller for the next season, but for demonstration purposes, I made a couple examples that should be easier to follow.

592 Note Field

SELECT bib, LEFT(isbn_list, LEN(isbn_list)-1) AS isbn_list
FROM (
	SELECT bib, (
		SELECT isbn.processed + ';' AS [text()]
		FROM isbn
		WHERE isbn.bib# = bibs_to_use.bib
		FOR XML PATH('')
	) as isbn_list
	FROM (
		SELECT b.bib# bib
		FROM bib b
		WHERE b.tag='592'
			AND b.text LIKE '%New and Forthcoming Fiction Winter 2011%'
	) as bibs_to_use
) as outter_query;

082 Call Number

SELECT bib, LEFT(isbn_list, LEN(isbn_list)-1) AS isbn_list
FROM (
	SELECT bib, (
		SELECT isbn.processed + ';' AS [text()]
		FROM isbn
		WHERE isbn.bib# = bibs_to_use.bib
		FOR XML PATH('')
	) as isbn_list
	FROM (
		SELECT b.bib# bib
		FROM bib b
		WHERE b.tag='082'
			AND b.text LIKE '%005%'
	) as bibs_to_use
) as outter_query;

260 Publisher

SELECT bib, LEFT(isbn_list, LEN(isbn_list)-1) AS isbn_list
FROM (
	SELECT bib, (
		SELECT isbn.processed + ';' AS [text()]
		FROM isbn
		WHERE isbn.bib# = bibs_to_use.bib
		FOR XML PATH('')
	) as isbn_list
	FROM (
		SELECT b.bib# bib
		FROM bib b
		WHERE b.tag='260'
			AND b.text LIKE '%Little, Brown and Co%'
	) as bibs_to_use
) as outter_query;

To be fair, I used an old example from a blog called Rational Relational and then converted that over to our needs in Horizon.

Update – 21 Jan 11

Here’s a little update that adds in a similar method for authors. It is very common to need to list authors in a comma seperated list, and it was a little tricky for me, so I’d assume it could be very tough for some readers.

SELECT bib, LEFT(isbn_list, LEN(isbn_list)-1) AS isbn_list,LEFT(author_list, LEN(author_list)-1) AS author_list
FROM (
	SELECT bib, (
		SELECT isbn.processed + ';' AS [text()]
		FROM isbn
		WHERE isbn.bib# = bibs_to_use.bib
		FOR XML PATH('')
	) as isbn_list, (
		SELECT a.processed + ';' AS [text()]
		FROM bib_auth ba, author a
		WHERE ba.bib# = bibs_to_use.bib
			AND a.auth# = ba.auth#
		FOR XML PATH('')
	) as author_list
	FROM (
		SELECT b.bib# bib
		FROM bib b
		WHERE b.tag='260'
			AND b.text LIKE '%Little, Brown and Co%'
	) as bibs_to_use
) as outter_query;