January 30, 2011 at 9:32 pm
Hi everyone, I have a problem. I need to transpose some data and here is an example.
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[testing]') AND type in (N'U'))
begin
create table testing(f1 int not null primary key,f2 varchar(10) null)
insert dbo.testing
select 1 f1, 'January' f2
union select 2 f1, 'February' f2
union select 3 f1, 'March' f2
union select 4 f1, 'April' f2
union select 5 f1, 'May' f2
union select 6 f1, 'June' f2
union select 7 f1, 'July' f2
union select 8 f1, 'August' f2
union select 9 f1, 'September' f2
union select 10 f1, 'October' f2
union select 11 f1, 'November' f2
union select 12 f1, 'December' f2
end
What I need is:
Month Nr 1 2 3 4 5 6 7 8 9 10 11 12
Month Name JanuaryFebruaryMarchApril May JuneJulyAugustSeptemberOctoberNovemberDecember
Is there anyone who can help me. This is rather urgent please!
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
January 30, 2011 at 10:18 pm
How about this?
select
'MonthNr' ColDesc,
max(case when f1 = '1' then cast(f1 as varchar) end) as [1],
max(case when f1 = '2' then cast(f1 as varchar) end) as [2],
max(case when f1 = '3' then cast(f1 as varchar) end) as [3],
max(case when f1 = '4' then cast(f1 as varchar) end) as [4],
max(case when f1 = '5' then cast(f1 as varchar) end) as [5],
max(case when f1 = '6' then cast(f1 as varchar) end) as [6],
max(case when f1 = '7' then cast(f1 as varchar) end) as [7],
max(case when f1 = '8' then cast(f1 as varchar) end) as [8],
max(case when f1 = '9' then cast(f1 as varchar) end) as [9],
max(case when f1 = '10' then cast(f1 as varchar) end) as [10],
max(case when f1 = '11' then cast(f1 as varchar) end) as [11],
max(case when f1 = '12' then cast(f1 as varchar) end) as [12]
from testing2
union all
select
'MonthName' ColDesc,
max(case when f2 = 'January' then f2 end) as [1],
max(case when f2 = 'February' then f2 end) as [2],
max(case when f2 = 'March' then f2 end) as [3],
max(case when f2 = 'April' then f2 end) as [4],
max(case when f2 = 'May' then f2 end) as [5],
max(case when f2 = 'June' then f2 end) as [6],
max(case when f2 = 'July' then f2 end) as [7],
max(case when f2 = 'August' then f2 end) as [8],
max(case when f2 = 'September' then f2 end) as [9],
max(case when f2 = 'October' then f2 end) as [10],
max(case when f2 = 'November' then f2 end) as [11],
max(case when f2 = 'December' then f2 end) as [12]
from testing2
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply