how to create a new column in my select query for the result set displayed.

See more:

SQL-Server-2012

Hi All

i need to add a new columns in my select result based on specific values in a column.

CustomerID	ParcelID   CustStatus	OrderID	OrderParcelID	ParcelStatus
S10000024	2010885	   OPEN	        31345	0	        SUSPENDED
S10000024	2010885	   OPEN	        31346	0	        SUSPENDED
S10000024	2010885	   OPEN	        31344	2010885	        ACTIVE
S10000081	2010891	   OPEN	        31365	0	        SUSPENDED
S10000081	2010891	   OPEN	        31366	0	        SUSPENDED
S10000081	2010891	   OPEN	        31362	2010891	        ACTIVE
S10000081	2010891	   OPEN	        31363	2010891	        SUSPENDED
S10000081	2010891	   OPEN	        31364	2010891	        ACTIVE

I have the able table from below SQL

select A.CUstomerID,P.ParcelID,C.[Status] as CustStatus,OT.OrderID,
OT.ParcelID as OrderParcelID, P.[Status] as ParcelStatus,
from customer C
join ordertable OT
on C.CustomerID = OT.CustomerID
join Parcel P
on P.ParcelID= OT.ParcelID

Now I want SQL to display the result set in below format

CustomerID  ParcelID	CustStatus  OrderID OrderParcelID ParcelStatus	ProcStatus
S10000024   2010885	OPEN	    31345   0	          SUSPENDED	SUSPENDED
S10000024   2010885	OPEN	    31346   0	          SUSPENDED	SUSPENDED
S10000024   2010885	OPEN	    31344   2010885	  ACTIVE	ACTIVE
S10000081   2010891	OPEN	    31365   0	          SUSPENDED	SUSPENDED
S10000081   2010891	OPEN	    31366   0	          SUSPENDED	SUSPENDED
S10000081   2010891	OPEN	    31362   2010891	  ACTIVE	ACTIVE
S10000081   2010891	OPEN	    31363   2010891	  SUSPENDED	ACTIVE
S10000081   2010891	OPEN	    31364   2010891	  ACTIVE	ACTIVE

i.e if we observe 3 columns from above table (CustomerID,OrderParcelID and PracelStatus) we need to added new column called ProcStatus, where this column has to get the status as Active even though it's status in ParcelStatus column is SUSPENDED.
and that is only when a customer has at least one Active status in ParcelStatus for any OrderID that has same/common in OrderParcelID.

Please help me here.

Thanks
chiru