Tranpose Column values into Column name

Question

Post reply

Tranpose Column values into Column name

karthik M

One Orange Chip

Points: 29139
More actions
September 8, 2011 at 1:18 am

#390364

Hi All,
I have to transpose column values into column name. I see there is a little bit trick in that one.
Table Structure:
Create table sec_perf
(
sec_id int,
perf_dt datetime,
perf_return decimal(16,8)
)
insert into sec_perf
select 1,'01/31/2011', 0.323
union
select 1,'02/28/2011', 1.112
union
select 1,'03/31/2011', -0.323
union
select 1,'04/30/2011', -0.002
union
select 1,'05/31/2011', 2.343
union
select 1,'06/30/2011', 1.333
union
select 1,'07/31/2011', 1.232
union
select 1,'08/31/2011', 0.323
union
select 2,'06/30/2010', 1.333
union
select 2,'07/31/2010', 1.232
union
select 2,'08/31/2010', 0.323
union
select 2,'07/31/2010', 1.232
union
select 2,'08/31/2010', 0.323
union
select 2,'09/30/2010', 0.323
union
select 2,'10/31/2010', 1.232
union
select 2,'11/30/2010', 0.323
union
select 2,'12/31/2010', 1.232
union
select 2,'01/31/2011', 0.323
union
select 2,'02/28/2011', 1.112
union
select 2,'03/31/2011', -0.323
union
select 2,'04/30/2011', -0.002
union
select 2,'05/31/2011', 2.343
union
select 2,'06/30/2011', 1.333
union
select 2,'07/31/2011', 1.232
union
select 2,'08/31/2011', 0.323
union
select 3,'01/31/2000',1.343
union
select 3,'08/31/2011',2.343
Expected output:
Sec_Id Jan/2000 Feb/2000 ....Jun/2010 ..... Aug/2011
1 null null null 0.323
2 null null 1.333 0.323
3 1.343 null null 2.343
Inputs are welcome!
karthik

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

karthik M One Orange Chip Points: 29139 More actions · Answer 1

In future, if there is any security added with 01/31/1998 date, i have to display from jan/1998 to till date.

karthik

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Karthik

Track down and read Jeff Moden's excellent article "Cross tabs and pivots".

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

CoolingPipe(Scott) Newbie Points: 3 More actions · Answer 3

declare @sql varchar(max)

set @sql = 'select Sec_Id'

select @sql = @sql + ',

max(case perf_dt when cast(''' + convert(varchar(10),perf_dt,101)

+ ''' as datetime) then perf_return else null end) [' + convert(varchar(10),perf_dt,101) + ']'

from (select distinct perf_dt from sec_perf) as a

set @sql = @sql + '

from sec_perf group by sec_id'

--print @sql

exec(@sql)

Because my MSSQL2005 version is for chinese, so i can't format the Column Name to like 'JAN/2010' directly.

Is it pivotal?

karthik M One Orange Chip Points: 29139 More actions · Answer 4

When we use PIVOT we have to mention the date in the IN clause. Right?

But in our case we don't know the available dates. It may vary every month.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 5

CoolingPipe(Scott) (9/8/2011)
declare @sql varchar(max) set @sql = 'select Sec_Id' select @sql = @sql + ', max(case perf_dt when cast(''' + convert(varchar(10),perf_dt,101) + ''' as datetime) then perf_return else null end) [' + convert(varchar(10),perf_dt,101) + ']' from (select distinct perf_dt from sec_perf) as a set @sql = @sql + ' from sec_perf group by sec_id' --print @sql exec(@sql)
Because my MSSQL2005 version is for chinese, so i can't format the Column Name to like 'JAN/2010' directly.
Is it pivotal?

The query is working..but it is not giving the expected result. Yes, I have to generate the missing date too in the report. Please verify the expected output.

karthik

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

karthikeyan-444867 (9/8/2011)
CoolingPipe(Scott) (9/8/2011)
declare @sql varchar(max) set @sql = 'select Sec_Id' select @sql = @sql + ', max(case perf_dt when cast(''' + convert(varchar(10),perf_dt,101) + ''' as datetime) then perf_return else null end) [' + convert(varchar(10),perf_dt,101) + ']' from (select distinct perf_dt from sec_perf) as a set @sql = @sql + ' from sec_perf group by sec_id' --print @sql exec(@sql)
Because my MSSQL2005 version is for chinese, so i can't format the Column Name to like 'JAN/2010' directly.
Is it pivotal?
The query is working..but it is not giving the expected result. Yes, I have to generate the missing date too in the report. Please verify the expected output.

Read the article, Karthic. This is trivial stuff for someone who's been hanging around here as long as you have. Don't forget to preaggregate the data before pivoting.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

karthik M One Orange Chip Points: 29139 More actions · Answer 7

oops..i just read the Part 1 article. Anyway, I have gone through the part-2. I think it will fit for my requirement. I will try and get back here.

karthik