use servicedw
--======Completed Confs parts & Qty=====--
SELECT DISTINCT
	CRM_PartsLabor.TRANSACTION_ID as [Service Order ID]
	, CRM_PartsLabor.ORDERED_PROD as [Part No]
	, CRM_PartsLabor.DESCRIPTION as [Part Desc]
	, CRM_Confirmations.POSTING_DATE as [Date Consumed]
	, CRM_StatusCodes.USER_STATUS as [Part Status]
	, CRM_PartsLabor.QUANTITY as [Quantity]
	, CRM_StatusCodes.END_DATE as [Service Order Last Change]
	, CRM_Orders.SERIAL as [Serial No]
        , CRM_Partners.DESCRIPTION 
	, CRM_Partners.ADDRESS as [Role]
	, CRM_Orders.PROCESS_TYPE

FROM
	CRM_PartsLabor INNER JOIN CRM_Orders ON 
	CRM_PartsLabor.TRANSACTION_ID = CRM_Orders.TRANSACTION_ID
	INNER JOIN CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID
	INNER JOIN CRM_Confirmations ON 
	CRM_StatusCodes.TRANSACTION_ID = CRM_Confirmations.TRANSACTION_ID
	INNER JOIN CRM_Partners ON CRM_Orders.PARTNER_ID = CRM_Partners.PARTNER_ID
WHERE
	CRM_PartsLabor.TRANSACTION_ID like ('3%')
	and CRM_StatusCodes.user_STATUS = 'Complete'
	and CRM_PartsLabor.ORDERED_PROD NOT IN ('AI','AP','BEAM0030_SVC_PLAN','BEAM0090_SVC_PLAN',
		'BEAM0150_SVC_PLAN','BEAM0300_SVC_PLAN','BEAM0600_SVC_PLAN','BEAM1200_SVC_PLAN','CALLCENTER_LABOR',
		'CLINICALAPPS_LABOR','CLINICALAPPS_TASK','DATA','DIST_SLF_APAC','DIST_TLC_APAC',
		'FSE_LABOR','H-5007-0000','H-5008-0000','INSTALL_TASK_LIST','INSTALLATION_TASK',
		'ISP_TASK','KB','MEDPHYSICS_LABOR','MEDPHYSICS_TASK','PARTNERSHIP_TLC','PROJECT_MNGT_TASK',
		'SERVICE LABOR','TOTAL_TLC_EMEA','TOTAL_TLC_NA','WARRANTY_APAC','WARRANTY_DIST_APAC',
		'WARRANTY_DIST_EMEA','WARRANTY_EMEA','WARRANTY_NA')
	and CRM_Partners.PARTNER_FCT IN ('00000001','00000056','00000052')

--===== Conditionally drop, then recreate and populate the test table.
     -- None of this is part of the solution.  It just provides a 
     -- test bed for the coded solution that follows this section.
     IF OBJECT_ID('TempDB..#PivotExample') IS NOT NULL
        DROP TABLE #PivotExample
 CREATE TABLE #PivotExample (PartNo VARCHAR(12),RoleDesc VARCHAR(64))
 INSERT INTO #PivotExample 
 SELECT '102031'     , 'Exec. Service Employee, Jeff A.'            UNION ALL
 SELECT '102031'     , 'Service Employee Group, Global Call Center' UNION ALL
 SELECT '102031'     , 'Sold-To Party, McLaren Center'              UNION ALL
 SELECT '102503'     , 'Exec. Service Employee, Jeff A.'            UNION ALL
 SELECT '102503'     , 'Service Employee Group, Global Call Center' UNION ALL
 SELECT '102503'     , 'Sold-To Party, Center'                      UNION ALL
 SELECT '5-903-61587', 'Exec. Service Employee, Jeff A.'            UNION ALL
 SELECT '5-903-61587', 'Service Employee Group, Global Call Center' UNION ALL
 SELECT '5-903-61587', 'Sold-To Party, Center'

--===== Solution starts here
--===== Conditionally drop the work table
     IF OBJECT_ID('Tempdb..#NormalNVP') IS NOT NULL
        DROP TABLE #NormalNVP

--===== Declare the dynamic SQL Variables
DECLARE @SQLSelect     VARCHAR(8000),
        @SQLSelectList VARCHAR(8000),
        @SQLFrom       VARCHAR(8000)

--===== Split the RoleDesc and save the data in a temp table because
     -- we're going to use it more than once.  We could use the a 
     -- CTE twice, but the code for it would be executed twice and
     -- it would simply make life a bit more difficult.  Temp table
     -- is a lot easier here because it allows for very simple
     -- "Divide'n'Conquer" programming... might be faster, too.
 SELECT *
   INTO #NormalNVP
   FROM (--==== Split the RoleDesc column like it should have been split in the 
             -- original NVP (Name/Value Pair) table.
         SELECT PartNo,
                SUBSTRING(RoleDesc,1,CHARINDEX(',',RoleDesc)-1) AS NVPRole,
                SUBSTRING(RoleDesc,CHARINDEX(',',RoleDesc)+2,8000) NVPValue
           FROM #PivotExample
        ) d

--===== Create the static part of the SELECT
 SELECT @SQLSelect     = ' SELECT PartNo,' + CHAR(10)

--===== Create the dynamic SELECT list
 SELECT @SQLSelectList = ISNULL(@SQLSelectList + ',' + CHAR(10),'') + SPACE(8)
                       + 'MAX(CASE WHEN NVPRole = ' + QUOTENAME(NVPRole,'''')
                       + ' THEN NVPValue END) AS '  + QUOTENAME(NVPRole) 
   FROM #NormalNVP
  GROUP BY NVPRole

--===== Create the static FROM clause
SELECT @SQLFrom = '
   FROM #NormalNVP
  GROUP BY PartNo
  ORDER BY PartNo
'
--===== Display the Dynmamic SQL we just created
  PRINT @SQLSelect + @SQLSelectList + @SQLFrom

--===== Execute the Dynamic SQL to solve the problem
   EXEC (@SQLSelect + @SQLSelectList + @SQLFrom)

--===== Solution starts here
--===== Conditionally drop the work table
     IF OBJECT_ID('Tempdb..#NormalNVP') IS NOT NULL
        DROP TABLE #NormalNVP

--===== Declare the dynamic SQL Variables
DECLARE @SQLSelect     VARCHAR(8000),
        @SQLSelectList VARCHAR(8000),
        @SQLFrom       VARCHAR(8000)

--===== Split the RoleDesc and save the data in a temp table because
     -- we're going to use it more than once.  We could use the a 
     -- CTE twice, but the code for it would be executed twice and
     -- it would simply make life a bit more difficult.  Temp table
     -- is a lot easier here because it allows for very simple
     -- "Divide'n'Conquer" programming... might be faster, too.
SELECT     CRM_PartsLabor.TRANSACTION_ID AS [Service Order ID], CRM_PartsLabor.ORDERED_PROD AS [Part No], 
                      CRM_PartsLabor.DESCRIPTION AS [Part Desc], CRM_Confirmations.POSTING_DATE AS [Date Consumed], 
                      CRM_StatusCodes.USER_STATUS AS [Part Status], CRM_PartsLabor.QUANTITY AS Quantity, 
                      CRM_StatusCodes.END_DATE AS [Service Order Last Change], CRM_Orders.SERIAL AS [Serial No], CRM_Orders.PROCESS_TYPE, 
                      CRM_Partners.PARTNER_FCT, CRM_Partners.DESCRIPTION, CRM_Partners.ADDRESS
INTO            [#NormalNVP]
FROM         CRM_PartsLabor INNER JOIN
                      CRM_Orders ON CRM_PartsLabor.TRANSACTION_ID = CRM_Orders.TRANSACTION_ID INNER JOIN
                      CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID INNER JOIN
                      CRM_Confirmations ON CRM_StatusCodes.TRANSACTION_ID = CRM_Confirmations.TRANSACTION_ID INNER JOIN
                      CRM_Partners ON CRM_Orders.PARTNER_ID = CRM_Partners.PARTNER_ID	select * from #NormalNVP

   (--==== Split the RoleDesc column like it should have been split in the 
             -- original NVP (Name/Value Pair) table.
         SELECT [Part No],
                SUBSTRING(RoleDesc,1,CHARINDEX(',',RoleDesc)-1) AS NVPRole,
                SUBSTRING(RoleDesc,CHARINDEX(',',RoleDesc)+2,8000) NVPValue
           FROM #PivotExample
        ) d

--===== Create the static part of the SELECT
 SELECT @SQLSelect     = ' SELECT PartNo,' + CHAR(10)

--===== Create the dynamic SELECT list
 SELECT @SQLSelectList = ISNULL(@SQLSelectList + ',' + CHAR(10),'') + SPACE(8)
                       + 'MAX(CASE WHEN NVPRole = ' + QUOTENAME(NVPRole,'''')
                       + ' THEN NVPValue END) AS '  + QUOTENAME(NVPRole) 
   FROM #NormalNVP
  GROUP BY NVPRole

--===== Create the static FROM clause
SELECT @SQLFrom = '
   FROM #NormalNVP
  GROUP BY PartNo
  ORDER BY PartNo
'
--===== Display the Dynmamic SQL we just created
  PRINT @SQLSelect + @SQLSelectList + @SQLFrom

--===== Execute the Dynamic SQL to solve the problem
   EXEC (@SQLSelect + @SQLSelectList + @SQLFrom)

quote:

---

Originally posted by visakh16

you've not specified how the three tables are related to each other. by which field?

---

FROM
	CRM_PartsLabor INNER JOIN CRM_Orders ON 
	CRM_PartsLabor.TRANSACTION_ID = CRM_Orders.TRANSACTION_ID
	INNER JOIN CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID
	INNER JOIN CRM_Confirmations ON 
	CRM_StatusCodes.TRANSACTION_ID = CRM_Confirmations.TRANSACTION_ID
	INNER JOIN CRM_Partners ON CRM_Orders.PARTNER_ID = CRM_Partners.PARTNER_ID