| 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.
|
|
|
|
|
RESOLVED- Transpose Columns to Rows - SQL 2000