Inverting table - SQL Server Forums

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

SQL Server Development (2000)

Inverting table

Author

Topic

Vishakha
Starting Member

40 Posts

Posted - 2006-12-14 : 01:04:04

I have a table with 12 rows, one for each month and 10 columns.
This table is used for reporting yearly data on per month basis.

Is there a way in which I can invert the table and output 10 rows with 12 columns, as this is the requirement for my report.

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-14 : 01:06:57

Please post your table structure and expected output. This seems like Cross-Tab candidate.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-12-14 : 01:16:10

If u r using Crystal as reporting tool, u can use Cross Tab type report where u can specify, which data should be printed as Row and Columns.

Mahesh

Vishakha
Starting Member

40 Posts

Posted - 2006-12-14 : 01:19:53

Current table

Columns - month count1 total1 count2 total2 .....count10 total10
rows - jan janc1 janr1 janc2 jant2 ........janc10 jant10
.
.
.
.
. dec decc1 decr1 decc2 dect2 ........decc10 dect10

Desired table

jan feb mar ...... dec
janc1............decc1
jant1............dect1
.
.
.
.
janc10............decc10
jant10 ...........dect10

How do I achieve this....

Thanks

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-12-14 : 01:34:23

Thats why I was asking, which reporting tool u r using. It possible in Crystal Reports with Cross Tab Reports

Mahesh

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-14 : 01:42:14

Something like this may be:

select
	sum(case [month] when 'jan' then count1 end) as Jan,
	sum(case [month] when 'feb' then count1 end) as Feb,
	sum(case [month] when 'Mar' then count1 end) as Mar
	....
from tbl
union all
select
	sum(case [month] when 'jan' then count2 end) as Jan,
	sum(case [month] when 'feb' then count2 end) as Feb,
	sum(case [month] when 'Mar' then count2 end) as Mar
	...
from tbl
....

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Vishakha
Starting Member

40 Posts

Posted - 2006-12-14 : 06:10:06

we are using crystal reports, but we the person working on crystal reports doesn't know how to use cross tab, we basically map the columns that query returns to the columns in the report.

that's why we are looking at something what harsh suggested, that might not be the best answer.

Thanks, we will definitely look into cross tab feature also

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-14 : 10:21:55

1 If you dont know how to use Cross-tab reports in Crystal Reports, read it in help file
2 Also read about Cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources