RESOLVED- Transpose Columns to Rows - SQL 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

RESOLVED- Transpose Columns to Rows - SQL 2000

Author

Topic

Mits
Starting Member

48 Posts

Posted - 2009-10-06 : 06:22:52

Hi
Here is my table

Field1--|--Field2-----|--Field3---|-Field4
A---------06-09-09-------100.00------5
B---------05-09-09-------200.00------6

I would like to display the data as follows

--------------A----------------B
Field2------06-09-09--------05-06-09
Field3------100.00-----------200.00
Field4--------5--------------6

Any ideas?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-06 : 06:56:16

Read about Cross-tab reports in SQL Server help file

Madhivanan

Failing to plan is Planning to fail

Mits
Starting Member

48 Posts

Posted - 2009-10-06 : 07:13:54

Thanks madhivanan. I have read this before posting and I might be wrong but does not solve my problem.

First of all there will be always 4 rows for each year and the SQL statement to insert data becomes static. In my case number of rows are dynamic.
I hope that makes sense.

Mits
Starting Member

48 Posts

Posted - 2009-10-06 : 11:36:41

Thanks guys.
I have managed to solve this myself.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-06 : 11:39:40

>>I have managed to solve this myself.
That's good!

Some other folks could benefit if you post your solution

Be One with the Optimizer
TG

Mits
Starting Member

48 Posts

Posted - 2009-10-06 : 12:02:25

My problem was I needed the values of Field1 to become the Column Headings and rest of the columns as row headings and numbers of rows will vary every time but will never be more then 10 rows.

1) Created a temp table

Create Table #TempTab
(Col_Heading varchar(50) NULL,
Col_Description varchar(50) NULL)

1) I dumped all the data that i needed into another temp table(#NewTempTab).
2) Looped through #TempTab and added the value of field 1 as Column in #TempTab

set @Sql = 'Alter Table #TempTab Add '
set @Sql = @SQL + rtrim(VALUE of FIELD1) + ' varchar(30) NULL'
exec(@SQL)

So Now my #TempTab will look as follows

Col_Heading , Col_Description , VALUE1 of FIELD1, VALUE1 of FIELD1..

3) Looped through #NewTempTab inserted the records
set @SQL = 'Insert Into #TempTab(Col_Heading,Col_Description, Order_id, ' + rtrim(VALUE1 of FIELD1) + ')'

set @NewSQL = @SQL + 'values(''Field2'', ''Field 2 Description''' + ',' + ',''' + rtrim(VALUE1 of FIELD1) + ''')'

exec(@NewSQL)

So now my #TempTab will look like

Col_Heading , Col_Description , A, -----------B
Field2, Field 2 Description-----06-09-09--------05-06-09
Field3, Field 3 Description-----100.00-----------200.00
Field4, Field 4 Description-------5--------------6

I hope it makes sense as the stored procedure has lots of code. But this just the jist of the solution.

Mits

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-12 : 08:13:38

quote:
Originally posted by Mits

Hi
Here is my table

Field1--|--Field2-----|--Field3---|-Field4
A---------06-09-09-------100.00------5
B---------05-09-09-------200.00------6

I would like to display the data as follows

--------------A----------------B
Field2------06-09-09--------05-06-09
Field3------100.00-----------200.00
Field4--------5--------------6

Any ideas?

What did you change?

Madhivanan

Failing to plan is Planning to fail

Mits
Starting Member

48 Posts

Posted - 2009-10-12 : 08:46:26

Hi Madhivanan, not exactly sure what you are asking.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources