Transposing the data in the table

Question

Post reply

Transposing the data in the table

samiigom-798993

Grasshopper

Points: 10
More actions
August 29, 2008 at 3:20 am

#192115

I need to transpose the data in the table
The table is something like
Seq_no Row_On_Dom Dow_On_Dom
12172 34
24545 56
I want this to be like
Seq_no 1 2
Row_On_Dom 2172 4545
Dow_On_Dom 34 56
Can anybody suggest how should I go in this case.
I do use SQL server 2000.

Viewing 4 posts - 1 through 3 (of 3 total)

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

David Jackson SSCertifiable Points: 6414 More actions · Answer 1

You can find the best info on how to do this in this article[/url]

HTH

Dave J

http://glossopian.co.uk/
"I don't know what I don't know."

Sriram.RM Hall of Fame Points: 3368 More actions · Answer 2

this is a better option... this works well for 3 columns... was developed by my friend.... good one...

create the below table

PIVOTTABLE

Col1varchar(100)

col2varchar(100)

Col3varchar(100)

create Proc Usp_PivotRep

as

declare @col varchar(100) -- Variable to get the second column while looping to build the query

declare @Sql1 varchar(7000) -- Variable to build the Sum of Case when Query

declare @Sql2 varchar(1000) -- Variable to build the heading Row

declare @sqlfull varchar(8000)

declare @Sql varchar(8000)

declare colcur cursor for select distinct col2 from PivotTable -- Cursor to get the Distinct Heading

set @Sql2='select ''header'' as header ' --Building the heading row ,''total'' as total,''sum'' as sum

set @Sql='select col1 as header' --Building the main query ,cast(count(*) as varchar(100)) as Total,cast(sum(cast(col3 as int)) as varchar(100)) Sum

open colcur

fetch next from colcur into @col

while @@Fetch_status = 0

begin

if @Col<>''

begin

set @Sql2= @Sql2 + ','''+@col+''' as ['+@col + ']' --Building the heading query

set @Sql= @Sql + ', cast( sum ( Case when col2='''+@col+'''then col3 else 0 end ) as varchar(100)) as ['+@col +']' --Building the Case when query

end

fetch next from colcur into @col

end

CLOSE colcur

deallocate colcur

set @Sql= @Sql + ' from PivotTable group by col1 '

--print @Sql

--print @Sql2

set @sqlfull= @Sql --Concatinating the Heading query and the Main Query with union all @sql2 +' union all ' +

exec(@sqlfull)

Jus fill the values in the pivottable table and run de proc... works real fast..

[font="Times New Roman"]For better assistance in answering your questions
Click Here[/url][/font]

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

GOD (8/29/2008)
this is a better option... this works well for 3 columns... was developed by my friend.... good one...
Jus fill the values in the pivottable table and run de proc... works real fast..

Jus run de proc and get de wrong results...

header 2172 4545

------ ---- ----

1 34 0

2 0 56

A far better option than getting the wrong results would be to follow the advice given by David and read Jeff Moden's recent excellent article about cross tabs. It will have you up and running in no time.

Cheers

ChrisM

^{“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