Ektron Asset Library List

SELECT (
		SELECT lib_type
		FROM libtype
		WHERE libtype.libtype_id = l.libtype_id
		) AS LibType,
	lib_id AS 'Library ID',
	lib_title AS 'Library Title',
	[filename] AS 'Library URL',
	CASE
		WHEN libtype_id < 3
			THEN CASE
					WHEN filename LIKE '%.%'
						THEN RIGHT(filename, Len(filename) - Charindex('.', filename))
					ELSE filename
					END
		END AS FileExt,
	content_id AS 'Content ID',
	(
		SELECT content_title
		FROM content
		WHERE content_id = l.content_id
		) AS 'Content Title',
	parent_id AS 'Parent ID',
	(
		SELECT DISTINCT CASE
				WHEN parent_id > 0
					THEN (
							SELECT DISTINCT folder_name
							FROM library_folder_tbl lft2
							WHERE lft1.parent_id = lft2.folder_id
							) + ' - ' + lft1.folder_name
				ELSE lft1.folder_name
				END AS FolderName
		FROM library_folder_tbl lft1
		WHERE folder_id = l.parent_id
		) AS 'Folder Name',
	date_created AS 'Date Created',
	--user_id AS 'Created By ID',
	(
		SELECT (first_name + ' ' + last_name)
		FROM users
		WHERE user_id = l.user_id
		) AS 'Created By',
	last_edit_date AS 'Last Edit Date',
	(l.[last_edit_fname] + ' ' + l.[last_edit_lname]) AS 'Last Editor',
	CASE content_type
		WHEN - 1
			THEN CAST(content_type AS VARCHAR(5)) + ' - AllTypes'
		WHEN 1
			THEN CAST(content_type AS VARCHAR(5)) + ' - Content'
		WHEN 2
			THEN CAST(content_type AS VARCHAR(5)) + ' - Forms'
		WHEN 3
			THEN CAST(content_type AS VARCHAR(5)) + ' - Archive Content'
		WHEN 4
			THEN CAST(content_type AS VARCHAR(5)) + ' - Archive Forms'
		WHEN 7
			THEN CAST(content_type AS VARCHAR(5)) + ' - PDF Assets'
		WHEN 8
			THEN CAST(content_type AS VARCHAR(5)) + ' - Assets'
		WHEN 9
			THEN CAST(content_type AS VARCHAR(5)) + ' - Archive Assets'
		WHEN 12
			THEN CAST(content_type AS VARCHAR(5)) + ' - Archive Media'
		WHEN 99
			THEN CAST(content_type AS VARCHAR(5)) + ' - Non Library Content'
		WHEN 101
			THEN CAST(content_type AS VARCHAR(5)) + ' - MS Office Assets'
		WHEN 102
			THEN CAST(content_type AS VARCHAR(5)) + ' - PDF Assets'
		WHEN 103
			THEN CAST(content_type AS VARCHAR(5)) + ' - PDF Assets'
		WHEN 106
			THEN CAST(content_type AS VARCHAR(5)) + ' - PNG Image Assets'
		WHEN 104
			THEN CAST(content_type AS VARCHAR(5)) + ' - Multimedia'
		WHEN 1102
			THEN CAST(content_type AS VARCHAR(5)) + ' - Archived PDF Assets'
		WHEN 1111
			THEN CAST(content_type AS VARCHAR(5)) + ' - Discussion Topic'
		WHEN 3333
			THEN CAST(content_type AS VARCHAR(5)) + ' - Catalog Entry'
		ELSE CAST(content_type AS VARCHAR(5))
		END AS 'Content Type'
FROM library l
ORDER BY 'Folder Name',
	LibType,
	parent_id