How to join one table to another that needs pivoting?

  • Hi All,

    I've inherited a database that contains VMWare server information such as CPU, RAM, Disks, etc.

    And I need to run a monthly report that shows the server spec for each VM.

    VCenter is polled for data every hour and the info on the VMs is recorded in a SQL Server database.

    So at the end of each month i've got approx. 700 rows of data - CPU, RAM, etc - for each VM and I need to report on the largest value for each column.

    This is because, If for example a department increases RAM from 32Gb to 64Gb they will be charged for the larger value for the entire month.

    Simple enough, where it gets tricky is for the storage info which is kept in a separate table.

    Each VM has an ID so you can identify which disks are associated to it, however the data for each is disk is recorded in separate rows.

    So for example, a VM could have 6 disks associated with it. So every hour 6 rows of data are inserted into the db for that VM showing the disk size & storage tier.

    I want the report to show the max values for all data items on a single line (rather than have 6 lines for each server where the only data changing is the disk size).

    This is not a problem for CPU, RAM, etc but it order to get the disk info on a single line, i'm guessing the simplest way is to use PIVOT ?

    Where i'm getting confused is how I can join my server and disk info tables together when one needs pivoting.

    So if I query table A for server info i get back

    VM_IDServerNameCPURAMPLATFORMReplicated

    1 MyServer 448WindowsY

    If I query TABLE B I get

    VM_IDDISK_IDSizeTier

    1 1234/15002

    1 1234/210242

    1 1234/34002

    The end result should look like:

    VM_IDServerNameCPURAMPLATFORMReplicatedDisk 1 Size Disk 1 TierDisk 2 Size Disk 2 TierDisk 3 SizeDisk 3 Tier

    1 MyServer1 448WindowsY500 2 1024 2 4002

    So I need a report that shows 1 line for each server with the max value for each item displayed for that month (except tier which will be min value).

    Also, as disks can be added, it needs to be dynamic to pick up additional disk ID's as they appear.

    Any suggestions on the best way to achieve this?

    Many thanks!

  • Just pivot your table inside a CTE or subquery (derived table) before joining to the other table.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks for the reply.

    However the requirement has now changed...

    Rather than having the server and disk data displayed together they now need to be on separate lines.

    Just to re-iterate...

    I have 2 tables, 1 for server info and 1 for disk info.

    Server info has columns like Server Name, CPU, RAM, etc and disk info has columns like Size & Tier.

    The server info is all in 1 row but the disk info will have a row for each disk.

    There is an ID column I can join the 2 tables with.

    I need to write a query which displays all the server info for each server on one line with the associated disk info below it for all the servers in the table.

    Eg:

    ServerCPURAMO/S

    MyServer1 432Windows

    DiskIDSizeTier

    1234/15002

    1234/22502

    1234/310242

    ServerCPURAMO/S

    MyServer2 14Linux

    DiskIDSizeTier

    5678/11002

    5678/22502

    etc, etc...

    I'm just having a brain fart today and can't get my head around it.

    Any suggestions on how best to do this?

  • Given that a query returns a result set, and you're looking for two result sets per server, this isn't really possible. Certainly it isn't easy - you're trying to use a query as a solution to a presentational problem. You'd be much better off using a tool such as Reporting Services or Excel to pivot your data for you.

    John

  • Stueyd (1/11/2017)


    Thanks for the reply.

    However the requirement has now changed...

    Rather than having the server and disk data displayed together they now need to be on separate lines.

    Just to re-iterate...

    I have 2 tables, 1 for server info and 1 for disk info.

    Server info has columns like Server Name, CPU, RAM, etc and disk info has columns like Size & Tier.

    The server info is all in 1 row but the disk info will have a row for each disk.

    There is an ID column I can join the 2 tables with.

    I need to write a query which displays all the server info for each server on one line with the associated disk info below it for all the servers in the table.

    Eg:

    ServerCPURAMO/S

    MyServer1 432Windows

    DiskIDSizeTier

    1234/15002

    1234/22502

    1234/310242

    ServerCPURAMO/S

    MyServer2 14Linux

    DiskIDSizeTier

    5678/11002

    5678/22502

    etc, etc...

    I'm just having a brain fart today and can't get my head around it.

    Any suggestions on how best to do this?

    This sounds more like a "presentation" thing. I would suggest you just write a normal query that will have all the data for the server in every record, and each record will only differ by virtue of which disk data it has. Then you can use SSRS to do the prettying up it needs to look that way. Look at either a tablix or matrix to do the job within SSRS, and you may not even need either one if you do the grouping on the server level.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • sgmunson (1/11/2017)


    This sounds more like a "presentation" thing. I would suggest you just write a normal query that will have all the data for the server in every record, and each record will only differ by virtue of which disk data it has. Then you can use SSRS to do the prettying up it needs to look that way. Look at either a tablix or matrix to do the job within SSRS, and you may not even need either one if you do the grouping on the server level.

    I was just going to post something very similar. So this is another vote for this option.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks guys, that sounds like a plan. I'll try it !

  • Quick update in case anyone was wondering.
    The last 3 comments were spot on.
    SSRS came to the rescue and I was able to grab all the data with a query and then chop it up and display it as required.
    Thanks for your help.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply