[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 607
  • Last Modified:

SQL Pivot - Rows to columns

Hi all,


I have a table called 'Disk_Info' that basically looks like this:

SystemName    DeviceID      Size      . . .     . . . (other irrelevant columns)
 System1              C             12888980992
 System1              D             64428552192
 System1              E             6432133120
 System2              C             5354622976
 System2              L             15035777024
 System3              C             27924791296
 ...
  ...



I need to output it via a sql query into the following format:

SystemName      DISK0     DISK1     DISK2     DISK3     DISK4       DISK(n)...    
  System1             C: 14gb   D: 20gb
  System2             C: 14gb   D: 20gb    E: 14gb   P: 20gb
  System3             C: 14gb   D: 20gb    E: 14gb   P: 20gb   L: 100gb
  System4             C: 14gb  
   . . .                      . . .
   . . .  

So the source table Disk Size is in bytes but for gigabytes it just needs to be divided like: Size/1024/1024/1024 = GB and rounded to the nearest whole number.

The number of 'Disks' per system also vary, however I don't need anymore disk info beyond disk 9 so Disk0 - Disk8 would suffice.

This only needs to work on MS SQL Server 2008 and doesn't need to be backwards compatible.

Thanks in advance!
Mike
0
mikeyd234
Asked:
mikeyd234
  • 4
  • 3
1 Solution
 
lwadwellCommented:
try along the lines of:

SELECT SystemName, [C] as DISK0, [D] as DISK1, [E] as DISK2, [F] as DISK3
FROM (SELECT SystemName, DeviceID, Size/1024/1024/1024 Size FROM Disk_Info)v
PIVOT(SUM(Size) FOR DeviceID IN ([C],[D],[E],[F]))p
0
 
mikeyd234Author Commented:
Thanks lwadwell for the response, I did try something similar to your example earlier, the problem is that the DISK drive letters have gaps, i.e one system could have C, D then J device id's only and I would need 'J' to appear as disk3, not disk10.
0
 
lwadwellCommented:
Then I would suggest something like:

SELECT SystemName, [1] as DISK0, [2] as DISK1, [3] as DISK2, [4] as DISK3
FROM (SELECT SystemName
                       , row_number()over(partition by SystemName order by DeviceID) DiskNo
                       , Size/1024/1024/1024 Size FROM Disk_Info)v
PIVOT(SUM(Size) FOR DiskNo IN ([1],[2],[3],[4]))p
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
mikeyd234Author Commented:
Aha awesome! Last question I promise :)

How would I get it to display the DeviceID as well as the Cieling(Size) in the output - as shown in my example output table above, i.e. 'C: 14gb'
0
 
lwadwellCommented:
build the string in the inline view, i.e.
    DeviceId+': '+convert(varchar, Size/1024/1024/1024)+'GB' as Size
0
 
mikeyd234Author Commented:
Quick responses, thank you!
0
 
mikeyd234Author Commented:
Thanks I have accepted your solution, here is the final SQL query:

SELECT SystemName, [1] as DISK0, [2] as DISK1, [3] as DISK2, [4] as DISK3, [5] as DISK4, [6] as DISK5, [7] as DISK6, [8] as DISK7, [9] as DISK8
FROM (SELECT SystemName
                       , row_number()over(partition by SystemName order by DeviceID) DiskNo
                       , ltrim(rtrim(DeviceId))+': '+convert(varchar, ceiling(Size/1024/1024/1024))+'GB' as Size FROM Disk_Info)v
PIVOT(MAX(Size) FOR DiskNo IN ([1],[2],[3],[4],[5],[6],[7],[8],[9]))p
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now