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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy