
; WITH CTE_PROJ AS (

	SELECT PROJECT_ID from PROJECT 
	UNION 
	SELECT '*' as PROJECT_ID from PROJECT
),

CTE_ALL_PROJ_WITH_PREF AS (

	SELECT  PC.PROJECT_ID, PC.PREF_NAME, PC.PREF_VALUE
	FROM	PROJ_CONFIG PC 
	WHERE PC.PROJECT_ID = '*'

)

SELECT	P.PROJECT_ID as PROJECT_ID,
		PC.PREF_NAME, 
		PC.PREF_VALUE  
FROM CTE_PROJ P
CROSS JOIN CTE_ALL_PROJ_WITH_PREF PC
	 


-- FINAL OUTPUT

--	PROJECT_ID,		PARENT_LINKS_IN,	PARENT_LINKS, ............................
--	*				0					1








declare @Columns nvarchar(max)

declare @query nvarchar(max)

select @Columns = STUFF((SELECT ',' + QUOTENAME( CONCAT( CASE WHEN CLASS_ID = '*' THEN 'ALL' ELSE CLASS_ID END, 
														'_', 
														PC.PREF_NAME) ) 
            FROM PROJ_CONFIG PC 
			WHERE PC.PROJECT_ID = '*'
			ORDER BY ZZ_ID
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @query = 'SELECT PROJECT_ID, ' + @Columns + '  from 
            (
                SELECT     P.PROJECT_ID ,
                                        PC.PREF_NAME, 
                                        PC.PREF_VALUE  
                           FROM (
									 SELECT PROJECT_ID from PROJECT 
									 UNION 
									 SELECT ''*'' as PROJECT_ID from PROJECT
								 ) P 
                           CROSS JOIN (  
										SELECT  PC.PROJECT_ID, PC.PREF_NAME, PC.PREF_VALUE
										FROM   PROJ_CONFIG PC 
										WHERE PC.PROJECT_ID = ''*''
								) PC
           ) T
            pivot 
            (
                 MIN(T.PREF_VALUE)
                for T.PREF_NAME in (' + @Columns + ')
            ) p '

--INSERT INTO #PROJ_CONF (PROJECT_ID,	ALL_PARENT_LINKS_IN,	ALL_PARENT_LINKS,	ALL_CHILD_LINKS_IN,	ALL_CHILD_LINKS,	ALL_LOGICAL_LINK_IN,	ALL_LOGICAL_LINK,	ALL_RELATED_ITEM_IN,	ALL_RELATED_ITEM,	ALL_RELATED_DOCS_IN,	
--						ALL_RELATED_DOCS,	ALL_RELATED_FLDR_IN,	ALL_RELATED_FLDR,	ALL_RELATED_SUB_IN,	ALL_RELATED_SUB,	ALL_RELATED_FILE_IN,	ALL_RELATED_FILE,	ALL_RELATED_EOS_IN,	ALL_RELATED_EOS,	
--						ALL_RELATED_NOTES_IN,	ALL_RELATED_NOTES,	ALL_CHILD_NOT_RELEASE_EVENT,	ALL_DOC_SON_STATUS,	ALL_ITM_SON_STATUS,	ALL_FLD_SON_STATUS,	ALL_EFFE_MODE,	ALL_UNDO_MODE,	
--						ALL_CHK_DIRECTION,	ALL_GROUP_TOP,	ALL_TOP_COMMIT,	ALL_GRP_MODE,	ALL_MAIL_MODE,	ALL_RELEASE_AREA,	ALL_ARCHIVE_AREA,	ALL_NETNODE_KEY,	ALL_AREA_NAME,	ALL_PRE_RELEASE_AREA,
--						ALL_PRE_NETNODE_KEY,	ALL_PRE_AREA_NAME,	ALL_DEFAULT_WFD_HOST,		ALL_ON_EXT_FAILURE,	ALL_RELATED_EUS_IN,	ALL_RELATED_EUS,	ALL_RELATED_MTRL_IN,	ALL_RELATED_MTRL,	ALL_MANUAL_REVISION,	
--						DOC_PARENT_LINKS_IN,	ITEM_PARENT_LINKS_IN,	FOLDER_PARENT_LINKS_IN,		DOC_PARENT_LINKS,	ITEM_PARENT_LINKS,	FOLDER_PARENT_LINKS,	ITEM_CHILD_LINKS_IN,	
--						DOC_CHILD_LINKS_IN,	FOLDER_CHILD_LINKS_IN,	ITEM_CHILD_LINKS,	DOC_CHILD_LINKS,	FOLDER_CHILD_LINKS,	DOC_LOGICAL_LINK_IN,	FOLDER_LOGICAL_LINK_IN,	DOC_LOGICAL_LINK,	
--						FOLDER_LOGICAL_LINK,	DOC_RELATED_ITEM_IN,	FOLDER_RELATED_ITEM_IN,		DOC_RELATED_ITEM,	FOLDER_RELATED_ITEM,	ITEM_RELATED_DOCS_IN,	FOLDER_RELATED_DOCS_IN,	
--						ITEM_RELATED_DOCS,	FOLDER_RELATED_DOCS,	ITEM_RELATED_FLDR_IN,	ITEM_RELATED_FLDR,	DOC_RELATED_FLDR_IN,	DOC_RELATED_FLDR,	DOC_RELATED_FILE_IN,	DOC_RELATED_FILE,	
--						ALL_RELATED_ECM_IN,	ALL_RELATED_ECM,	ALL_RELATED_PDM_IN,	ALL_RELATED_PDM,	ALL_RELATED_REF_IMPACTED_IN,	ALL_RELATED_REF_IMPACTED,	ALL_RELATED_REF_DRIVER_IN,	ALL_RELATED_REF_DRIVER,	
--						ALL_RELATED_DRAWING_IN,	ALL_RELATED_DRAWING,	ALL_RELATED_MODEL_IN,		ALL_RELATED_MODEL,	ITEM_RELATED_DRAWING_IN,	ITEM_RELATED_DRAWING,	DOC_RELATED_MODEL_IN,	
--						DOC_RELATED_MODEL
--)
execute(@query)




