Click here to Skip to main content
14,766,261 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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 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
Posted
Updated 30-Sep-15 2:20am
v2

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
   
Comments
aarif moh shaikh 30-Sep-15 8:59am
   
agree with you
Maciej Los 30-Sep-15 12:15pm
   
Well... Thank you.
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


after this you will get output like
justone colum'
9/6/2015
9/8/2015
9/15/2015


step 2: converting rows to column
for this you can use pivot table.
i found a related article for this
checkout
Simple Way To Use Pivot In SQL Query
   
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.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900