无法使用group by子句组合行 [英] unable to combine rows using group by clause

问题描述

我想在sql server 2005中将三行合并为一行，我尝试使用group by子句，但我无法将这三行合并为一行

I want to combine three rows as a single row in sql server 2005, i have tried with group by clause, but i could not combine the three rows into a single row

select * from 
(
select 
case 
   when  postatus = 'y' and  approverid = '1111' 
   then Convert(varchar(10),  trasdate ,103) 
   end as [Purchase_Approval_date],

  case
   when  postatus = 'y' and  approverid = '401'
   then Convert(varchar(10),  trasdate ,103)
   end as [Capex_Approval_date],

  case
   when  postatus = 'y' and ( approverid = '329' or  approverid = '1495' or  approverid = '1239')
   then Convert(varchar(10),  trasdate ,103)
   end as [Finance_Approval_Date]

from tblpo_approvalstatus where prnumber = '000002'
) as t
group by
Purchase_Approval_date,
Capex_Approval_date,
Finance_Approval_Date

i得到的结果为

i got the result as

Purchase_Approval_date	Capex_Approval_date	Finance_Approval_Date
9/6/2015	NULL	NULL
NULL	9/8/2015	NULL
NULL	NULL	9/15/2015

但我希望将三行合并为一行

but i want to combine three rows as single row

Purchase_Approval_date	Capex_Approval_date	Finance_Approval_Date
9/6/2015	        9/8/2015                         9/15/2015

提前致谢

Thanks in advance

推荐答案

试试这个：

Try this:

select MAX([Purchase_Approval_date]) AS [Purchase_Approval_date], MAX([Capex_Approval_date]) AS [Capex_Approval_date], MAX([Finance_Approval_Date]) AS [Finance_Approval_Date]
FROM
(
select
  case
   when  approverid = '1111'
   then Convert(varchar(10),  trasdate ,103)
   end as [Purchase_Approval_date],

  case
   when  approverid = '401'
   then Convert(varchar(10),  trasdate ,103)
   end as [Capex_Approval_date],

  case
   when approverid = '329' or  approverid = '1495' or  approverid = '1239'
   then Convert(varchar(10),  trasdate ,103)
   end as [Finance_Approval_Date]

FROM tblpo_approvalstatus
WHERE prnumber = '000002' AND postatus = 'y' 
) as t

这可以通过以下步骤完成

第1步：不要在主查询中选择3列，而是使用嵌套的case语句来查找

Purchase_Approval_date，

Capex_Approval_date，

Finance_Approval_Date

this can be done at following steps
step 1: do not select 3 columns in the main query, instead use nested case statement to find out
Purchase_Approval_date,
Capex_Approval_date,
Finance_Approval_Date

    CASE
        WHEN condition 
        THEN
            CASE
                WHEN condition1 
                THEN
                    CASE 
                        WHEN condition2
                        THEN calculation1
                        ELSE calculation2
                    END
                ELSE
                    CASE 
                        WHEN condition2
                        THEN calculation3
                        ELSE calculation4
                    END
            END
        ELSE 
            CASE 
                WHEN condition1 
                THEN 
                    CASE
                        WHEN condition2 
                        THEN calculation5
                        ELSE calculation6
                    END
                ELSE
                    CASE
                        WHEN condition2 
                        THEN calculation7
                        ELSE calculation8
                    END
            END            
    END AS 'justone colum'
from your Table

之后你会得到像
$ b的输出$ b justone colum'

9/6/2015

2015/9/15

9/15/2015





第2步：将行转换为列

为此你可以使用数据透视表。

i found一篇相关的文章

结账

在SQL查询中使用Pivot的简单方法

如果每个prnumber存在3条记录。

一条用于approverid ='1111'

一个用于approverid ='401'

和一个用于当approverid ='329'或approverid ='1495'或approverid ='1239'



您可以使用表别名和连接，如下所示：

If 3 records exist for each prnumber.
one for approverid = '1111'
one for approverid = '401'
and one for when approverid = '329' or approverid = '1495' or approverid = '1239'

You could possible use table aliases and joins, something like this:

select 
	PurchaseDate.prnumber,
	PurchaseDate.trasdate Purchase,
	CapexDate.trasdate Capex,
	FinanceDate.trasdate Finance
from (select * 	from tblpo_approvalstatus 
			where approverid = '1111' and postatus = 'y'
		) PurchaseDate
left join (select * from tblpo_approvalstatus 
			where approverid = '401' and postatus = 'y'
		) CapexDate
	on PurchaseDate.prnumber = CapexDate.prnumber
left join (select * from tblpo_approvalstatus 
			where approverid = '329' or  approverid = '1495' or  approverid = '1239' 
			and postatus = 'y'
		) FinanceDate
	on PurchaseDate.prnumber = FinanceDate.prnumber
;
--using left join, assuming a record always is present when approverid = '1111'

希望它有所帮助。

Hope it helps out.

这篇关于无法使用group by子句组合行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！