Ektron Menu Item List

List out all items in an Ektron menu

SELECT mt.[mnu_id] AS 'Menu ID'
	,mnu_name AS 'Menu Name'
	,[item_title] AS 'Menu Item Title'
	,[item_link]
	,CASE [item_target]
		WHEN 1
			THEN 'Popup'
		WHEN 2
			THEN 'Self'
		WHEN 3
			THEN 'Parent'
		WHEN 4
			THEN 'Top'
		END AS 'Item Target'
	,
	--[item_target],
	[item_id] AS 'Content ID'
	,c.[content_title] AS 'Content Title'
	,
	--  ,[item_type],
	CASE item_type
		WHEN - 1
			THEN CAST(item_type AS VARCHAR(5)) + ' - AllTypes'
		WHEN 1
			THEN CAST(item_type AS VARCHAR(5)) + ' - Content'
		WHEN 2
			THEN CAST(item_type AS VARCHAR(5)) + ' - Forms'
		WHEN 3
			THEN CAST(item_type AS VARCHAR(5)) + ' - Archive Content'
		WHEN 4
			THEN CAST(item_type AS VARCHAR(5)) + ' - Archive Forms'
		WHEN 7
			THEN CAST(item_type AS VARCHAR(5)) + ' - PDF Assets'
		WHEN 8
			THEN CAST(item_type AS VARCHAR(5)) + ' - Assets'
		WHEN 9
			THEN CAST(item_type AS VARCHAR(5)) + ' - Archive Assets'
		WHEN 12
			THEN CAST(item_type AS VARCHAR(5)) + ' - Archive Media'
		WHEN 99
			THEN CAST(item_type AS VARCHAR(5)) + ' - Non Library Content'
		WHEN 101
			THEN CAST(item_type AS VARCHAR(5)) + ' - MS Office Assets'
		WHEN 102
			THEN CAST(item_type AS VARCHAR(5)) + ' - PDF Assets'
		WHEN 103
			THEN CAST(item_type AS VARCHAR(5)) + ' - PDF Assets'
		WHEN 106
			THEN CAST(item_type AS VARCHAR(5)) + ' - PNG Image Assets'
		WHEN 104
			THEN CAST(item_type AS VARCHAR(5)) + ' - Multimedia'
		WHEN 1102
			THEN CAST(item_type AS VARCHAR(5)) + ' - Archived PDF Assets'
		WHEN 1111
			THEN CAST(item_type AS VARCHAR(5)) + ' - Discussion Topic'
		WHEN 3333
			THEN CAST(item_type AS VARCHAR(5)) + ' - Catalog Entry'
		ELSE CAST(item_type AS VARCHAR(5))
		END AS 'Content Type'
	,cft.FolderPath AS 'Content Folder Path'
	,[order_loc]
	,[item_description]
	,
	--[link_type],
	CASE
		WHEN link_type = 0
			THEN 'QuickLink'
		WHEN link_type = 1
			THEN 'Menu Template'
		END
--	[id]
FROM [HPM-DEV].[dbo].[menu_to_item_tbl] mtit
INNER JOIN menu_tbl mt ON mt.mnu_id = mtit.mnu_id
LEFT OUTER JOIN [content] c ON c.content_id = mtit.item_id
LEFT OUTER JOIN [content_folder_tbl] cft ON c.folder_id = cft.folder_id
ORDER BY mnu_name
	,mt.mnu_id
	,order_loc