SQL Qury - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

SQL Qury

Author

Topic

ravininave
Posting Yak Master

111 Posts

Posted - 2015-04-09 : 01:42:13

I've query like this


Select  TrID, Amount, Sum(GrowthAmt) As Growth, 'PRINCIPAL' as mHead from GrowthMast where TrDesc ='Commitment' and AppMstID =@MyCode and PaidTag=1 Group by TrID, Amount
UNION ALL Select  TrID, SUM(Amount), Sum(GrowthAmt) As Growth , 'PRINCIPAL PAID' as mHead from GrowthMast where TrDesc ='Commitment withdrawal' and AppMstID =@MyCode and PaidTag=1 Group by TrID, Amount
UNION ALL Select TrID, Amount, Sum(GrowthAmt) As Growth , 'GROWTH' as mHead from GrowthMast where TrDesc ='Growth' and AppMstID =@MyCode and PaidTag=1 Group by TrID, Amount
UNION ALL Select TrID, Amount, Sum(GrowthAmt) As Growth , 'GROWTH PAID' as mHead from GrowthMast where TrDesc ='Growth withdrawal' and AppMstID =@MyCode and PaidTag=1 Group by TrID, Amount

It returns row like
TrID Amount Growth mHead
Z505893464 45000 0 PRINCIPAL
Z347456519 100000 0 PRINCIPAL
Z505893464 200 0 PRINCIPAL PAID
Z347456519 500 0 PRINCIPAL PAID
Z505893464 45000 900 GROWTH
Z347456519 100000 8000 GROWTH
Z347456519 300 0 GROWTH PAID

Now I need final output like
TrID, Amout where mHead ='PRINCIPAL',Amout where mHead ='PRINCIPAL PAID', Amount where mHead ='GROWTH PAID'

Can anyone help?

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-09 : 11:23:39

Something like this:


with CTE as 
(
    .... your original query ....
)

select TrID, stuff(Amounts, 1,2,'')
from CTE a

cross apply
(
	select ', ' + cast(Amount as varchar(20)) + ' where mHead = ''' + mHead + ''''
	from CTE B
	where a.TrID = b.trid
	for xml path('')
) _(Amounts)

ravininave
Posting Yak Master

111 Posts

Posted - 2015-04-09 : 14:30:18

No, can you please describe it or put some example or code.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-09 : 16:35:23

That is sample code! Plug in your original query where I indicated and away you go.

The idea is to process the results of your original query, using FOR XML PATH('') to string together the "Amount where..." data

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources