Convert rows in to columns--Very Very Urgent

Question

Post reply

Convert rows in to columns--Very Very Urgent

Kiran Kumar MP

SSC Enthusiast

Points: 176
More actions
November 24, 2008 at 8:29 pm

#383453

Hi I have a table A
----------------------------------------
Date 1M 2m 3m 4m
----------------------------------------
20080523 1.2 2.1 3.7 1.7
20080524 1.3 2.6 3.0 1.2
20080525 1.2 2.3 3.2 1.5
20080526 1.7 2.8 3.2 1.3
My requirement is to get a temp table like
----------------------------------------------------------
Tenor 20080523 20080524 20080525 20080526
-----------------------------------------------------------
1m 1.2 1.3 1.2 1.7
2m 2.1 2.6 2.3 2.8
3m 3.7 3.0 3.2 3.2
4m 1.7 1.2 1.5 1.3
Could u please help us as it is very urgent.
Thanx
Kiran

Viewing 11 posts - 1 through 10 (of 10 total)

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

Kiran Kumar MP SSC Enthusiast Points: 176 More actions · Answer 1

I need a dynamic query that executes any number of rows.not for only four dates.

Kiran

krayknot SSCertifiable Points: 7998 More actions · Answer 2

Kiran Kumar MP (11/24/2008)
Hi I have a table A
----------------------------------------
Date 1M 2m 3m 4m
----------------------------------------
20080523 1.2 2.1 3.7 1.7
20080524 1.3 2.6 3.0 1.2
20080525 1.2 2.3 3.2 1.5
20080526 1.7 2.8 3.2 1.3
My requirement is to get a temp table like
----------------------------------------------------------
Tenor 20080523 20080524 20080525 20080526
-----------------------------------------------------------
1m 1.2 1.3 1.2 1.7
2m 2.1 2.6 2.3 2.8
3m 3.7 3.0 3.2 3.2
4m 1.7 1.2 1.5 1.3
Could u please help us as it is very urgent.
Thanx
Kiran

how many rows and what index of column(s) you want to convert in the columns. Im asking this as sql does not proviedes unlimited number of columns.

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Kiran Kumar MP SSC Enthusiast Points: 176 More actions · Answer 3

the table is having lakhs of rows.if i select a date range from 1-10

there should be fixed (Tenor) column for alll date ranges and other columns are dates basing on the range.

ie

if date range is (1-10)

Tenor 1 2 3 4 5 6 7 8 9 10

if date range is (1-5)

Tenor 1 2 3 4 5

like that.

Kiran

krayknot SSCertifiable Points: 7998 More actions · Answer 4

Kiran Kumar MP (11/24/2008)
the table is having lakhs of rows.if i select a date range from 1-10
there should be fixed (Tenor) column for alll date ranges and other columns are dates basing on the range.
ie
if date range is (1-10)
Tenor 1 2 3 4 5 6 7 8 9 10
if date range is (1-5)
Tenor 1 2 3 4 5
like that.
Kiran

give me 20 minutes . im near to completion

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

krayknot SSCertifiable Points: 7998 More actions · Answer 5

krayknot (11/24/2008)
Kiran Kumar MP (11/24/2008)
the table is having lakhs of rows.if i select a date range from 1-10
there should be fixed (Tenor) column for alll date ranges and other columns are dates basing on the range.
ie
if date range is (1-10)
Tenor 1 2 3 4 5 6 7 8 9 10
if date range is (1-5)
Tenor 1 2 3 4 5
like that.
Kiran
give me 20 minutes . im near to completion

Is this helpful

declare @colname varchar(100)

declare @rowcolname varchar(1000)

set @rowcolname = ''

declare col CURSOR FOR

select id from actionstate

open col

fetch next from col into @colname

while @@fetch_status = 0

begin

set @rowcolname = @rowcolname + '[' + @colname + '] int,'

fetch next from col into @colname

end

close col

deallocate col

print('Create TAble tablename('+ @rowcolname + ')')

exec('Create TAble tablename('+ @rowcolname + ')')

select * from tablename

drop tablename

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Kiran Kumar MP SSC Enthusiast Points: 176 More actions · Answer 6

Hi shitij

Could you please give me the structure of actionstate?

kiran

krayknot SSCertifiable Points: 7998 More actions · Answer 7

Kiran Kumar MP (11/24/2008)
Hi shitij
Could you please give me the structure of actionstate?
kiran

it has two columns

ID(int) IDDesc(varchar)

1 abcd

2 xyz

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Kiran Kumar MP SSC Enthusiast Points: 176 More actions · Answer 8

Kiran Kumar MP

SSC Enthusiast

Points: 176

November 24, 2008 at 11:10 pm

#902915

Thanx yaar

krayknot SSCertifiable Points: 7998 More actions · Answer 9

Kiran Kumar MP (11/24/2008)
Thanx yaar

will it work??

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 10

Kiran Kumar MP (11/24/2008)
Hi I have a table A
----------------------------------------
Date 1M 2m 3m 4m
----------------------------------------
20080523 1.2 2.1 3.7 1.7
20080524 1.3 2.6 3.0 1.2
20080525 1.2 2.3 3.2 1.5
20080526 1.7 2.8 3.2 1.3
My requirement is to get a temp table like
----------------------------------------------------------
Tenor 20080523 20080524 20080525 20080526
-----------------------------------------------------------
1m 1.2 1.3 1.2 1.7
2m 2.1 2.6 2.3 2.8
3m 3.7 3.0 3.2 3.2
4m 1.7 1.2 1.5 1.3
Could u please help us as it is very urgent.
Thanx
Kiran

you can use PIVOT option, if your database is SQL 2005 and above version.

for more info see SQL BOL.