Flattening a cube - 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 2005 Forums

Transact-SQL (2005)

Flattening a cube

Author

Topic

ipndmr1
Starting Member

3 Posts

Posted - 2009-03-05 : 15:05:09

To report out of a massive spreadsheet, I imported the data to SQL and used a Group By..Cube to create a view of the data that summarizes the amount storage on each tier (type of disk) in our SAN. Here is a sample of what the view looks like:

Hostname Disktype Size
-------- ----------- ------
Host1 146GB RAID1 120000
Host1 146GB RAID5 1000
Host1 300GB RAID5 1000
Host1 500GB RAID5 10000
Host2 300GB RAID5 1100

I need to get them into a "flattened" table like this:

Hostname Tier1 Tier2 Tier3 Tier4 Total
-------- ------ ----- ----- ----- ------
Host1 120000 1000 1000 10000 132000
Host2 1100 1100

I can write the algorithm easily enough, but my brain is just not coming up with the SQL to do it. It's almost to the point that I'm going to write it in C# and sod the SQL.

Thanks for any help you can give.

Daniel

subhash chandra
Starting Member

40 Posts

Posted - 2009-03-05 : 15:37:43

Use PIVOT functionality of T-SQL.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-05 : 15:40:48

He needs dynamic PIVOT I think...you can refer this

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

ipndmr1
Starting Member

3 Posts

Posted - 2009-03-06 : 09:48:33

Thanks, both of you. I was looking more at straight code rather than changing its 'geometry', but that is probably why I hit an impasse.

I'll post my results here after I try it out.

Thanks,
Daniel

ipndmr1
Starting Member

3 Posts

Posted - 2009-03-06 : 10:23:19

Fantastic - thanks to both of you. Using the dynamic pivot worked exactly as I needed it to. I used the code from Madhivanan to create my proc & executed with this code:

exec dynamic_pivot
'Select hostname,size from StorageMatrix',
'disktype',
'sum(size)'

The result was a flattened cube that I can use to support our capacity planning/reporting operations.

Have a great weekend,
Daniel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources