从具有两种不同条件的表中选择两次相同的列 [英] Select same column twice from a table with two different conditions
问题描述
我有一张桌子如下
ID HRS METHOD
------------------
1 2.5 A
1 5.0 B
2 0.5 A
2 1.5 B
3 3.25 A
我想获取以下记录。
所需输出:
ID HRS(A)HRS(B)
-------------------
1 2.5 5.0 >
1 0.5 1.5
1 3.25 NA
我使用了以下查询但是,我得到空值
SELECT EDT_RACNUM_ID,
CASE
WHEN EDT_PHASE_CD = ' 2300' 那么 EDT_HOURS_NUM
- ELSE EDT_HOURS_NUM
END AS < span class =code-string>' EDT_HOURS_NUM1',
CASE
WHEN EDT_PHASE_CD = ' 2301' 那么 EDT_HOURS_NUM
- ELSE EDT_HOURS_NUM
END AS ' EDT_HOURS_NUM2'
FROM P9T_ESTIMATE_DTL
ORDER BY EDT_RACNUM_ID
我得到的输出当我运行SQL时(NULL值不是desir ed):
ID HRS(A)HRS(B)
---------------------- -
1 9.1 NULL
1 6.0 NULL
1 NULL 6.3
1 NULL 1.2
Appreciaite你的帮助。请告诉我这是否可行?如果是,请提供示例SQL。
谢谢,
此查询处理ID值缺少一种方法(A或B)的情况。它通过使用SELECT DISTINCT并为唯一ID的每列选择Hours(A)值和Hours(B)值来实现。如果A或B缺少值,则结果为NULL。如果要显示除NULL以外的其他内容,请使用ISNULL()函数和CAST语句。请参阅此解决方案底部的示例。
选择 distinct t1.id as ID,
(从 table_1中选择小时 作为 T3 其中 T3.id = t1.id 和 T3.method = ' A') as [Hours(A)],
( Select 小时来自 table_1 作为 T4 其中 T4.id = t1.id 和 T4.method = ' B') as [Hours(B)]
from Table_1 as T1
结果
引用:ID小时(A)小时(A)
1 2.50 5.00
2 0.50 1.50
3 3.25 NULL
select distinct t1.id as ID,
ISNULL (CAST((选择小时来自 table_1 As T3 其中 T3.id = t1.id 和 T3.method = ' A') as varchar ( 6 )),' N / A') as [Hours(A)],
ISNULL(CAST((从 table_1中选择小时 作为 T4 其中 T4.id = t1.id 和 T4.method = ' B') as varchar ( 6 )),' N / A') as [Hours(A)]
from Table_1 as T1
结果
引用:ID小时(A)小时(A)
1 2.50 5.00
2 0.50 1.50
3 3.25 N / A
经测试:SQL Server Express 2012
_____________________________________________________________________________________________ _
SQL创建表语句
CREATE TABLE [dbo]。[Table_1](
[ID] [int] NULL,
[小时] [数字](8,2)NULL,
[方法] [nchar](1)NULL
)ON [PRIMARY]
用于创建测试数据的SQL插入语句
insert into table_1(id,hours,method) values ( 1 , 2 。 5 ,' A'跨度>);
插入 进入 table_1(id,hours,method)值( 1 , 5 。 0 ,' B');
插入 进入 table_1(id,hours,method)值( 2 , 0 。 5 ,' A');
插入 进入 table_1(id,hours,method)值( 2 , 1 。 5 ,' B');
插入 进入 table_1(id,hours,method)值( 3 , 3 。 25 ,' A');
感谢大家的帮助。
我用 Pivot 来计算它。
再次感谢你花时间尝试:)
请试试这个。除了NA,你将得到0.00
SELECT ID ,SUM(HRS_A)HRS_A,SUM(HRS_B)HRS_B
FROM
(
SELECT ID,
CASE WHEN METHOD = ' A' 那么 HRS ELSE 0 END HRS_A,
CASE WHEN METHOD = ' B' 那么 HRS ELSE 0 END HRS_B
FROM TABLE_1
)A
GROUP BY ID
Hi,
I have a table as below
ID HRS METHOD
------------------
1 2.5 A
1 5.0 B
2 0.5 A
2 1.5 B
3 3.25 A
I want to fetch records as following.
Desired Output:
ID HRS(A) HRS(B)
-------------------
1 2.5 5.0
1 0.5 1.5
1 3.25 NA
I used the follwing query but, I get null values
SELECT EDT_RACNUM_ID,
CASE
WHEN EDT_PHASE_CD = '2300' THEN EDT_HOURS_NUM
--ELSE EDT_HOURS_NUM
END AS 'EDT_HOURS_NUM1',
CASE
WHEN EDT_PHASE_CD = '2301' THEN EDT_HOURS_NUM
--ELSE EDT_HOURS_NUM
END AS 'EDT_HOURS_NUM2'
FROM P9T_ESTIMATE_DTL
ORDER BY EDT_RACNUM_ID
Ouput I get when I run the SQL(NULL values not desired):
ID HRS(A) HRS(B)
-----------------------
1 9.1 NULL
1 6.0 NULL
1 NULL 6.3
1 NULL 1.2
Appreciaite your help. Please tell me if this is possible or not? If yes, can you please provide the sample SQL.
Thanks,
This query handles the case in which one method (either A or B) is missing for an ID value. It does that by using SELECT DISTINCT and selecting the Hours(A) value and the Hours(B) value for each column for the unique ID. If a value is missing for either A or B, then the result is NULL. If you want something other than NULL to display, use the ISNULL() function and CAST statement. See example of that at the bottom of this solution.
select distinct t1.id as ID, (Select hours from table_1 As T3 where T3.id=t1.id and T3.method='A') as [Hours(A)], (Select hours from table_1 As T4 where T4.id=t1.id and T4.method='B') as [Hours(B)] from Table_1 as T1
Results
Quote:ID Hours(A) Hours(A)
1 2.50 5.00
2 0.50 1.50
3 3.25 NULL
select distinct t1.id as ID, ISNULL(CAST((Select hours from table_1 As T3 where T3.id=t1.id and T3.method='A') as varchar(6)),'N/A') as [Hours(A)], ISNULL(CAST((Select hours from table_1 As T4 where T4.id=t1.id and T4.method='B') as varchar(6)),'N/A') as [Hours(A)] from Table_1 as T1
Results
Quote:ID Hours(A) Hours(A)
1 2.50 5.00
2 0.50 1.50
3 3.25 N/A
Tested: SQL Server Express 2012
______________________________________________________________________________________________
SQL Create Table statement
CREATE TABLE [dbo].[Table_1]( [ID] [int] NULL, [Hours] [numeric](8, 2) NULL, [Method] [nchar](1) NULL ) ON [PRIMARY]
SQL Insert statements to create test data
insert into table_1 (id,hours,method) values(1,2.5,'A'); insert into table_1 (id,hours,method) values(1,5.0,'B'); insert into table_1 (id,hours,method) values(2,0.5,'A'); insert into table_1 (id,hours,method) values(2,1.5,'B'); insert into table_1 (id,hours,method) values(3,3.25,'A');
Thanks everyone for your help.
I figured it out using a Pivot.
Thanks again for taking time for trying :)
Please try this one. Except for "NA" you will get 0.00 there
SELECT ID, SUM(HRS_A) HRS_A, SUM(HRS_B)HRS_B FROM ( SELECT ID, CASE WHEN METHOD = 'A' THEN HRS ELSE 0 END HRS_A, CASE WHEN METHOD = 'B' THEN HRS ELSE 0 END HRS_B FROM TABLE_1 ) A GROUP BY ID
这篇关于从具有两种不同条件的表中选择两次相同的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!