March 19, 2011 at 12:50 am
Hi Frineds,
Can you please create below Sample1,Sample2 table as below
Create table Sample1
(
Name varchar(5),
[1] int,
[2] int,
[3] int
)
Insert into Sample1 values('AA',11,22,33)
Insert into Sample1 values('BB',20,30,40)
Insert into Sample1 values('CC',66,77,88)
Create table Sample2
(
Rowno int,
MDate datetime
)
Insert into Sample2 values(1,'05-01-2010')
Insert into Sample2 values(2,'06-02-2010')
Insert into Sample2 values(3,'07-03-2010')
Select * from Sample1
Select * from Sample2
But i want data as below format from both Sample1 and Sample2 tables.
Name 2010-05-01 00:00:00.000 2010-06-02 00:00:00.000 2010-07-03 00:00:00.000
AA 11 22 33
BB 20 30 40
CC 66 77 88
Anybody can help on this?
March 19, 2011 at 4:49 am
There are two ways to approach is problem
The first is to use pivot and unpivot such as
select Name,[2010-05-01 00:00:00.000],[2010-06-02 00:00:00.000],[2010-07-03 00:00:00.000] from
(
select s1.Name,s2.Mdate,s1.Value from
(
select Name,RowNo,Value from
(
select * from Sample1
)P1
unpivot
(
value for RowNo in ([1],[2],[3])
)c1
) s1 left join Sample2 s2 on s1.RowNo = s2.Rowno
) P2
pivot
(
sum(Value) for Mdate in ([2010-05-01 00:00:00.000],[2010-06-02 00:00:00.000],[2010-07-03 00:00:00.000])
)c2
This is what you might call the “correct” way to do it. You will have to make the query dynamic if your columns are going to change
However all you are attempting to do here is replace the column names.
This is another way to achieve the same result and should scale better for large sets
declare @Sql Varchar(8000)
set @Sql = 'Select Name'
declare @ColumnNames table
(
RowNumber int,
ColumnName varchar(50)
)
insert into @ColumnNames(RowNumber,ColumnName)
select ROW_NUMBER() over (order by RowNo),',[' + CAST(rowNo as varchar(5)) + '] [' + Convert(varchar(50),MDate ,121 ) + ']' from Sample2
declare @LoopCounter int
set @LoopCounter = 1
while((select COUNT(*) from @ColumnNames) >= @LoopCounter )
begin
set @Sql = @Sql + (select ColumnName from @ColumnNames where RowNumber = @LoopCounter)
set @LoopCounter = @LoopCounter + 1
end
set @Sql = @Sql + ' from Sample1'
exec (@SQL)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply