Rearraigning Table Data in Query Output.

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

General SQL Server Forums

New to SQL Server Programming

Rearraigning Table Data in Query Output.

Author

Topic

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-04-18 : 10:11:19

I am creating queries and reports in an Access front end (let the groans begin)...it is what they gave me to work with!

This particular query is pulling information from several different tables and manipulating it the way I want. However, there is one table that I am trying to pull info from that has the data arranged differently than all the others.

e.g.:

Name Drive Capacity Free_Space
Computer A C 40 10
Computer A D 200 175
Computer B C 80 55
Computer B D 500 445
Computer B E 500 365

I want that to look like:

Computer A C 40 10 D 200 10
Computer B C 80 55 D 500 445 E 500 365

I would prefer to do this within the existing query, without creating a new (permanent) table because the info needs to be real-time.

Any ideas/tips/leads?

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-18 : 10:16:55

What version?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-18 : 10:45:26

Here's something for 2005...

declare @t table (Name varchar(20), Drive char(1), Capacity int, Free_Space int)
insert @t
          select 'Computer A', 'C', 40, 10
union all select 'Computer A', 'D', 200, 175
union all select 'Computer B', 'C', 80, 55
union all select 'Computer B', 'D', 500, 445
union all select 'Computer B', 'E', 500, 365

select distinct Name, cast((select Drive + ' ' +
        right(space(5) + cast(Capacity as varchar(5)), 5) + ' ' + 
        right(space(5) + cast(Free_Space as varchar(5)), 5) + '    '
    from @t where Name = a.Name for xml path('')) as varchar(60)) as Details
from @t a

/* Results
Name                 Details
-------------------- ------------------------------------------------------------
Computer A           C    40    10    D   200   175    
Computer B           C    80    55    D   500   445    E   500   365    
*/

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-04-21 : 09:13:21

SQL Server 2K

If I am reading this correctly, I have to declare all of the computers. I really need this to be dynamic as we are adding and deleting machines all the time.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-21 : 09:33:46

Here's something for 2000...

-- Preparation
declare @t table (Name varchar(20), Drive char(1), Capacity int, Free_Space int)
insert @t
          select 'Computer A', 'C', 40, 10
union all select 'Computer A', 'D', 200, 175
union all select 'Computer B', 'C', 80, 55
union all select 'Computer B', 'D', 500, 445
union all select 'Computer B', 'E', 500, 365

-- Calculation
select
    Name,
    max(case DriveNumber when 1 then Drive else null end) as Drive1,
    max(case DriveNumber when 1 then Capacity else null end) as Capacity1,
    max(case DriveNumber when 1 then Free_Space else null end) as Free_Space1,
    max(case DriveNumber when 2 then Drive else null end) as Drive3,
    max(case DriveNumber when 2 then Capacity else null end) as Capacity2,
    max(case DriveNumber when 2 then Free_Space else null end) as Free_Space2,
    max(case DriveNumber when 3 then Drive else null end) as Drive3,
    max(case DriveNumber when 3 then Capacity else null end) as Capacity3,
    max(case DriveNumber when 3 then Free_Space else null end) as Free_Space3
from
    (select *, (select count(*) from @t where Name = a.Name and Drive <= a.Drive) as DriveNumber from @t a) a
group by Name

/* Results
Name                 Drive1 Capacity1   Free_Space1 Drive3 Capacity2   Free_Space2 Drive3 Capacity3   Free_Space3
-------------------- ------ ----------- ----------- ------ ----------- ----------- ------ ----------- -----------
Computer A           C      40          10          D      200         175         NULL   NULL        NULL
Computer B           C      80          55          D      500         445         E      500         365
*/

> If I am reading this correctly...
I don't think you were.

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-05-09 : 09:55:06

Well, I resolved it, and now fill somewhat less intelligent than before!

I created a query to cull each drive letter info and then used another query to pull all of that back together.

Query Drive A
Query Drive B
Query Drive C

Query those queries.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-05-09 : 12:20:14

GRAYWOLF - can you post the final queries that you created and that worked for you. Sharing the info can help someone else down the line!

Terry

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-05-09 : 13:24:41

sure....

I made a separate query for each drive letter:

SELECT TOP 100 PERCENT VCAdmin.VPX_ENTITY.NAME, CAST(ROUND(VCAdmin.VPX_GUEST_DISK.CAPACITY, 2) / 1073741824 AS decimal(4, 2)) AS Capacity,
CAST(ROUND(VCAdmin.VPX_GUEST_DISK.FREE_SPACE, 1) / 1073741824 AS decimal(4, 2)) AS Free
FROM VCAdmin.VPX_VM INNER JOIN
VCAdmin.VPX_ENTITY ON VCAdmin.VPX_VM.ID = VCAdmin.VPX_ENTITY.ID INNER JOIN
VCAdmin.VPX_GUEST_DISK ON VCAdmin.VPX_VM.ID = VCAdmin.VPX_GUEST_DISK.VM_ID
WHERE (VCAdmin.VPX_GUEST_DISK.PATH = 'c:\')
ORDER BY VCAdmin.VPX_ENTITY.NAME

Then created a query to query them:

SELECT TOP 100 PERCENT dbo.VM_C_Space.Capacity AS [C Cap],
dbo.VM_C_Space.Free AS [C Free], dbo.VM_D_Space.Capacity AS [D Cap], dbo.VM_D_Space.Free AS [D Free], dbo.VM_E_Space.Capacity AS [E Cap],
dbo.VM_E_Space.Free AS [E Free], dbo.VM_G_Space.Capacity AS [F Cap], dbo.VM_G_Space.Free AS [F Free], dbo.VM_F_Space.Capacity AS [G Cap],
dbo.VM_F_Space.Free AS [G Free], FROM VCAdmin.VPX_VM INNER JOIN
VCAdmin.VPX_ENTITY ON VCAdmin.VPX_VM.ID = VCAdmin.VPX_ENTITY.ID LEFT OUTER JOIN
dbo.VM_C_Space ON VCAdmin.VPX_ENTITY.NAME = dbo.VM_C_Space.NAME LEFT OUTER JOIN
dbo.VM_D_Space ON VCAdmin.VPX_ENTITY.NAME = dbo.VM_D_Space.NAME LEFT OUTER JOIN
dbo.VM_F_Space ON VCAdmin.VPX_ENTITY.NAME = dbo.VM_F_Space.NAME LEFT OUTER JOIN
dbo.VM_G_Space ON VCAdmin.VPX_ENTITY.NAME = dbo.VM_G_Space.NAME LEFT OUTER JOIN
dbo.VM_E_Space ON VCAdmin.VPX_ENTITY.NAME = dbo.VM_E_Space.NAME

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources