如何在我的选择查询中为显示的结果集创建新列。 [英] how to create a new column in my select query for the result set displayed.

问题描述

大家好



i需要根据列中的特定值在我的选择结果中添加新列。

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

我有下面的表格SQL

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

现在我希望SQL以下面的格式显示结果集

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

即如果我们观察到上表中的3列（CustomerID，Ord） erParcelID和PracelStatus）我们需要添加一个名为ProcStatus的新列，其中此列必须将状态设置为Active，即使它在ParcelStatus列中的状态为SUSPENDED。

仅当客户拥有时对于在OrderParcelID中具有相同/通用的任何OrderID，ParcelStatus中至少有一个活动状态。



请在这里帮助我。



谢谢

chiru

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

推荐答案

这篇关于如何在我的选择查询中为显示的结果集创建新列。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！