unable to combine rows using group by clause

Question

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

SQL

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 30-Sep-15 0:43am

yes Kumar

Updated 30-Sep-15 1:20am

Maciej Los

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2015-09-30T01:26:00

Try this:

SQL

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

amit raje · Answer 2 · 2015-09-30T01:46:00

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

SQL

    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

jaket-cp · Answer 3 · 2015-09-30T02:45:00

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:

SQL

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.

unable to combine rows using group by clause

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0