February 11, 2014 at 4:26 am
I have two tables as below
create table #table1 (id int,name varchar(10),[sep-12] varchar(10),[oct-12] varchar(10))
Create table #table2 (ID int,Abc numeric(10,3), cde numeric(10,3),monthnbr int,yearnbr int)
insert into #table1
values
(1,'Abc',null,null),
(2,'cde',null,null)
insert into #table2
values
(1,1.00,6.27,9,2012),
(2,2.10,8.20,10,2012)
i need output like
1,Abc,1.0,2.1
2,cde,6.27,8.2
Thanks in advance
February 11, 2014 at 4:42 am
all you need is a PIVOT on the query, my question is what happened if you have 2013 year data for same months then what will be your result set then ?
February 11, 2014 at 4:46 am
Thanks for your reply
if there is data for 2013 then it will be new row in #table2
9,2013
February 11, 2014 at 4:52 am
as per the design you will be adding 12 new columns in the table1 each year.
why you need a table1 in a first place, what exactly the problem you want to solve with this solution ?
February 11, 2014 at 4:56 am
In table1 i have 3 years of data in same format , I need to insert values from different tables so i will create one query that will populate table2. Now i need to fill those null values.
February 11, 2014 at 11:26 pm
following is the solution for your as per the provided sample data.
Declare @table1 table (ID int,name varchar(10),[sep-12] varchar(10),[oct-12] varchar(10))
Declare @table2 table (ID int,Abc numeric(10,3), cde numeric(10,3),monthnbr int,yearnbr int)
insert into @table1
values
(1,'Abc',null,null),
(2,'cde',null,null)
insert into @table2
values
(1,1.00,6.27,9,2012),
(2,2.10,8.20,10,2012)
SELECT name, sum([201209]) as [201209], sum([201210]) as [201210]
from (
SELECT id, name, value, colMonthYear, ROW_NUMBER() OVER ( ORDER BY ID) AS rownum
from(
SELECT b.id,b.Abc,b.cde, b.yearnbr * 100 + b.monthnbr AS colMonthYear
from @table2 b
) as p
unpivot
(
value for name in ( Abc,cde)
)p1
) AS t1
PIVOT
(
MAX(value) for colMonthYear in ([201209], [201210])
) AS T2
GROUP by name
hope it helps
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply