How to use pivot on a varchar column and display the result in a single row

See more:

SQL-Server

WITH CustTable AS(

			select 
			PAM.ProjectId,
			PD.Number AS OperationNumber,
			ND.NodeName,
			ND.NodeTypeId,
			NT.NodeType,
			PFM.Classification,
			CFD.Occurence,
			EFD.Sevearity,
			DCD.RPN,
			DCD.Target,
			AD.Detection,
			AD.EffectiveDate,
			AD.Occurence as ActionOccurance,
			AD.RPN as ActionRPN,
			AD.Sevearity AS ActionSevearity,
			AD.TargetDate,
			RANK() over (partition by NodeType  order by NodeName)R

			 from
			  FMEAMappings FM
			inner join ProjectAuthorizationMapping PAM
			ON PAM.ProjectAuthourizationId=FM.Id
			INNER JOIN NodeDetails ND
			ON ND.ProjectFMEAMapId = FM.FMEAMappingId 
			INNER join NodeTypes NT
			ON NT.NodeTypeId=ND.NodeTypeId
			LEFT outer JOIN OperationDetails PD
			ON PD.NodeId=ND.NodeId
			Left OUTER JOIN PotentialFailuerModeDetails PFM
			on PFM.NodeId=ND.NodeId
			LEFT OUTER JOIN CauseofFailureDetails CFD
			ON CFD.NodeId=ND.NodeId
			LEFT Outer JOIN EffectofFailureDetails EFD
			ON ND.NodeId=EFD.NodeId
			LEFT OUTER JOIN DetectionControlDetails DCD
			ON DCD.NodeId=ND.NodeId 
			LEFT OUTER JOIN ActionDetails AD
			on AD.NodeId=ND.NodeId
			where  PAM.ProjectId='P-ID1234'

                                      GROUP BY  PD.Number,PAM.ProjectId,
			ND.NodeName,
			ND.NodeTypeId,
			NT.NodeType,
			PFM.Classification,
			CFD.Occurence,
			EFD.Sevearity,
			AD.Detection,
			AD.EffectiveDate,
			AD.Occurence,
			AD.RPN ,
			AD.Sevearity ,
			AD.TargetDate,
			DCD.RPN,
			DCD.Target 
			)
			
	SELECT  
 		P1.[Process],P1. [Potential Failure Mode] ,p1.[Effects of Failures],
		                              P1. [Cause of Failures],p1.[Current Process Control],
		                               p1.OperationNumber,Occurence,Sevearity,P1.ProjectId,R,rpn,
		                               --P2. [Detection Control],
			--Target,
  		 p1.EffectiveDate,p1.ActionOccurance,Detection,p1.ActionRPN,p1.ActionSevearity,p1.TargetDate
  		 
 		  
 			 
		FROM
		  (SELECT DISTINCT ProjectId,OperationNumber,R,NodeName, Occurence,Sevearity,rpn,
		  					  EffectiveDate,ActionOccurance,Detection,ActionRPN,ActionSevearity,TargetDate,
		              PivotKey=NodeType from CustTable
	
	     	) I
		PIVOT 
		 (Min(NodeName)FOR PivotKey in([Process], [Potential Failure Mode] ,[Effects of Failures],
		                               [Cause of Failures],[Current Process Control] )) P1 order by r

i want it should display in a single row.

p1 f61| Potential F61 |Effect of f61 |cause of f61 |current f61 |60 |4|3|P-ID1234|2 |NULL |2012-06-08 00:00:00.000 |3| 4 |24| 2| 2012-07-08 00:00:00.000