Crosstable to Table (negative crosstable)

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)

Crosstable to Table (negative crosstable)

Author

Topic

vl262610
Starting Member

6 Posts

Posted - 2006-03-08 : 04:40:45

I need help converting a 'crosstable' (prox. 400 000 records) like:

Company AccountID Year Period1 Period2 Period3 Period4
3 1 2001 100 1000 5 20
3 1 2001 100 1000 50 20
3 1 2002 100 1000 5 20
3 1 2003 100 1000 5 20

converted to a simple table like:

Company AccountID Year Period Sum(Period)
3 1 2001 1 200
3 1 2001 2 2000
3 1 2001 3 55
3 1 2001 4 40
3 1 2002 1 100
3 1 2002 2 1000
3 1 2002 3 5
3 1 2002 4 40
3 1 2003 1 100
3 1 2003 2 1000
3 1 2003 3 5
3 1 2003 4 40

Hopefully somebody will be able to suggest a solution for this issue.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-03-08 : 04:54:11

[code]SELECT
mt.Company,
mt.AccountID,
mt.[Year],
1 AS Period,
SUM(mt.Period1) AS val
FROM
dbo.MyTable AS mt
GROUP BY
mt.Company,
mt.AccountID,
mt.[Year]
UNION ALL
SELECT
mt.Company,
mt.AccountID,
mt.[Year],
2 AS Period,
SUM(Period2) AS val
FROM
dbo.MyTable AS mt
GROUP BY
mt.Company,
mt.AccountID,
mt.[Year]
UNION ALL
SELECT
... etc.
[/code]

Mark

vl262610
Starting Member

6 Posts

Posted - 2006-03-08 : 05:10:49

Thanks Mark, already had figured out something like your answer by defining a couple of views for selecting the periods which were followed by union statements.

Problem is performance. Everytime the view is started my processor (2 intel 64 bit) reaches 100% for about 8 minutes while transforming 14 periods!! for 400 000 records so it finaly returns 5 600 000 rows.
Users are allowed to run this view whenever they like resulting in a disturbed performance for other users.

Hope somebody has a more optimal solution which runs a little faster.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-03-08 : 05:50:41

I've also used another solution which is neater in terms of code, but less performant. It's kind of unusual to be transforming this data into a more normalised structure for the purposes of reporting. How is the table populated in the first place? One would normally expect to see a more normalised structure for receiving the data. If changing the source table isn't an option, I'd suggest scheduling an overnight process to populate a persistant table with the desired structure and then have your users query this via a stored procedure.

vl262610
Starting Member

6 Posts

Posted - 2006-03-08 : 05:57:54

I know it's unusual to do this, but:
- source in ERP system is crosstable
- reporting needs to have a normalised table at first
- second the table is being converted to different format

In the mean time I was trying a lot of different scenario's.

Found the the real solution for me in using a new function in SQL2K5 -> UNPIVOT

like:

select company, accounid, period, value
from (select company, accounid, period1, period2, period3, period4 from table) p
unpivot
(value for period in
(period1, period2, period3, period4)
)as unpvt

this view does not have any utilization and is running almost as fast.

Thanks for replying.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources