Declare @ParentTable Table(RefNo nvarchar(50),Qty numeric(18,0)); Insert into @ParentTable Values ('001','50'); Insert into @ParentTable Values ('002','40'); Insert into @ParentTable Values ('003','100'); Declare @ChildTable Table(RefNo nvarchar(50),Typ nvarchar(50),Qty numeric(18,0)); Insert into @ChildTable Values ('001','A',10); Insert into @ChildTable Values ('001','B',30); Insert into @ChildTable Values ('002','C',10);
Select distinct T1.RefNo,T1.Qty, sum(case when T2.Typ='A' then T2.Qty else 0 end) as A, sum(case when T2.Typ='B' then T2.Qty else 0 end) as B, sum(case when T2.Typ='C' then T2.Qty else 0 end) as C, T1.Qty - ISNULL(sum(T2.Qty),0) as Balance from @ParentTable T1 left outer join @ChildTable T2 on T1.RefNo=T2.RefNo group by T1.RefNo,T1.Qty
order by T1.RefNo,T1.Qty
Priya_here
Member
709 Points
1859 Posts
Result set needed
Feb 09, 2013 09:04 AM|LINK
Hi..
this is my table1
RefNo Qty
001 50
002 40
003 100
Table2
RefNo Type Qty
001 A 10
001 B 30
002 C 10
Now i need result set like this
RefNo Qty A B C Balance
001 50 10 30 0 10
002 40 0 0 10 30
003 100 0 0 0 100
how would be my sql query
Thank you
Pbalan.in
Contributor
2142 Points
483 Posts
Re: Result set needed
Feb 09, 2013 09:45 AM|LINK
Priya Pls try this
Declare @ParentTable Table(RefNo nvarchar(50),Qty numeric(18,0));
Insert into @ParentTable Values ('001','50');
Insert into @ParentTable Values ('002','40');
Insert into @ParentTable Values ('003','100');
Declare @ChildTable Table(RefNo nvarchar(50),Typ nvarchar(50),Qty numeric(18,0));
Insert into @ChildTable Values ('001','A',10);
Insert into @ChildTable Values ('001','B',30);
Insert into @ChildTable Values ('002','C',10);
Select distinct T1.RefNo,T1.Qty,
sum(case when T2.Typ='A' then T2.Qty else 0 end) as A,
sum(case when T2.Typ='B' then T2.Qty else 0 end) as B,
sum(case when T2.Typ='C' then T2.Qty else 0 end) as C,
T1.Qty - ISNULL(sum(T2.Qty),0) as Balance
from @ParentTable T1 left outer join @ChildTable T2 on T1.RefNo=T2.RefNo group by T1.RefNo,T1.Qty
order by T1.RefNo,T1.Qty
Dont forget to mark as answer
Priya_here
Member
709 Points
1859 Posts
Re: Result set needed
Feb 09, 2013 09:51 AM|LINK
For example i have use A,B,C it can be any data so how would be my query...?
Pbalan.in
Contributor
2142 Points
483 Posts
Re: Result set needed
Feb 09, 2013 09:57 AM|LINK
For that you have to use temporary tables using SP
Priya_here
Member
709 Points
1859 Posts
Re: Result set needed
Feb 09, 2013 09:59 AM|LINK
can you post a complete code..
it wil;l help me
Thanks
sandeepmitta...
Contributor
6801 Points
1059 Posts
Re: Result set needed
Feb 09, 2013 10:44 AM|LINK
You would be required dynamic pivot
Refer below posts
http://itdeveloperzone.blogspot.in/2011/11/dynamic-pivot-in-sql-server.html
http://itdeveloperzone.blogspot.in/2011/01/pivot-with-dynamic-columns.html
Sandeep Mittal | My Blog - IT Developer Zone
Priya_here
Member
709 Points
1859 Posts
Re: Result set needed
Feb 09, 2013 10:49 AM|LINK
can you post a complete code it will help us
sandeepmitta...
Contributor
6801 Points
1059 Posts
Re: Result set needed
Feb 09, 2013 11:13 AM|LINK
create table #table1 ( RefNo varchar(10), Qty int) insert into #table1 select '001', 50 union all select '002', 40 union all select '003', 100 create table #table2( RefNo varchar(10), Type varchar(10), Qty int) insert into #table2 select '001', 'A', 10 union all select '001', 'B', 30 union all select '002', 'C', 10 declare @paramList varchar(max), @paramList1 varchar(max) set @paramList = stuff((select distinct ',[' + Type + ']' from #table2 for xml path('')),1,1,'') set @paramList1 = stuff((select distinct ',[' + Type + '] as [' + Type + ']'from #table2 for xml path('')),1,1,'') set @paramList1 = stuff(replace(replace(',' + @paramList1, ',[', ',isnull(['), '] ','],0) '),1,1,'') declare @query nvarchar(max) set @query = 'SELECT RefNo, Qty, ' + @paramList1 + ', Qty - (' + replace(replace(replace(@paramList,',','+'), '[', 'isnull(['), ']','],0)') + ') as Balance' + ' FROM( select a.RefNo, a.Qty, b.Type, b.Qty as Qty1 from #table1 a left join #table2 b on a.RefNo = b.RefNo )src PIVOT(SUM(Qty1) FOR Type IN (' + @paramList + ')) pvt order by RefNo' exec sp_executesql @query drop table #table1 drop table #table2 /* OUTPUT RefNo Qty A B C Balance ---------- ----------- ----------- ----------- ----------- ----------- 001 50 10 30 0 10 002 40 0 0 10 30 003 100 0 0 0 100 */Sandeep Mittal | My Blog - IT Developer Zone
Pbalan.in
Contributor
2142 Points
483 Posts
Re: Result set needed
Feb 09, 2013 11:20 AM|LINK
You can use the PIVOT table to convert rows into columns
rakeshsinha....
Member
30 Points
8 Posts
Re: Result set needed
Feb 09, 2013 11:40 AM|LINK
Hi,
You need to use temporary table if you using stored procedure in sql server or you can take record set in dataset then you can manuplate as you want.
Follow on Facebook
Follow on Twitter