August 2, 2008 at 5:22 am
Hi All,
I need to create a complex join, (in my eyes) 🙂
I want to create a query in my HP SIM db, so I can import the data into remedy (Helpdesk / managment system)
the challange is to only return one row for each server, also if ex. there are 3 logical drives for the same server in another tables, they need to return in different cols, in same row.
I don't know if its possible, but maybe do some kind of loop, to insert the multiple rows in cols in the result table...
hope it makes sens.!
This guy has the same problem, if he explains it better than me 🙂
http://bytes.com/forum/thread818249.html
PS it has to be done without any VB or any other, straight sql..
August 2, 2008 at 7:12 pm
What's the maximum number of logical drives per server?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 3, 2008 at 7:49 am
thats the problem, that its different every time, but 3-4 I think.. at most..
August 3, 2008 at 10:40 am
This should do it:
;With cteServers as (
Select server
, logical_disk
, Row_Number() Over(Partition by server, order by logical_disk) as N
From Servers
)
Select server
, Max(Case N When 1 Then logical_disk Else NULL End) as Disk1
, Max(Case N When 2 Then logical_disk Else NULL End) as Disk2
, Max(Case N When 3 Then logical_disk Else NULL End) as Disk3
, Max(Case N When 4 Then logical_disk Else NULL End) as Disk4
, Max(Case N When 5 Then logical_disk Else NULL End) as Disk5
From cteServers
Group by server
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 4, 2008 at 12:57 am
got some errors, tried to change it a bit, but still not the result I'm looking for, any ideas?
With cteServers as (
Select PC.PC
,PC.PC_DomainName
, Disks.Disk_Desc
, Row_Number() Over(Partition by Disks.Disk_Desc order by Disks.Disk_Desc) as N
From PC
LEFT JOIN Disks
ON PC.ID_PC = Disks.ID_Disk )
Select PC
, Max(Case N When 1 Then Disk_Desc Else NULL End) as Disk1
, Max(Case N When 2 Then Disk_Desc Else NULL End) as Disk2
, Max(Case N When 3 Then Disk_Desc Else NULL End) as Disk3
, Max(Case N When 3 Then Disk_Desc Else NULL End) as Disk4
, Max(Case N When 3 Then Disk_Desc Else NULL End) as Disk5
From cteServers
Group by Disk_Desc,PC
Result:
PC Disk1 Disk2 Disk3 Disk4 Disk5
SGH-TGH-T61PAA8634T MUW095P SCSI CdRom DeviceNULLNULLNULLNULL
SGH-TGH-T61PMATSHITA DVD-RAM UJ-852 NULLNULLNULLNULL
SGH-TGH-T61PST9100821AS NULLNULLNULLNULL
3 lines instead of 1 🙁
August 4, 2008 at 6:53 am
Try this instead:
With cteServers as (
Select PC.PC
,PC.PC_DomainName
, Disks.Disk_Desc
, Row_Number() Over(Partition by Disks.Disk_Desc order by Disks.Disk_Desc) as N
From PC
LEFT JOIN Disks
ON PC.ID_PC = Disks.ID_Disk)
, cteDisks as (
Select PC
, Case N When 1 Then Disk_Desc Else NULL End as Disk1
, Case N When 2 Then Disk_Desc Else NULL End as Disk2
, Case N When 3 Then Disk_Desc Else NULL End as Disk3
, Case N When 3 Then Disk_Desc Else NULL End as Disk4
, Case N When 3 Then Disk_Desc Else NULL End as Disk5
, N
From cteServers)
Select PC
, Max(Disk1) as Disk1
, Max(Disk2) as Disk2
, Max(Disk3) as Disk3
, Max(Disk4) as Disk4
, Max(Disk5) as Disk5
From cteDisks
Group by PC
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 4, 2008 at 1:04 pm
Oh my god.. 🙂 is it all in your head? :hehe:
I don't know why, but i wrote this instead,
, Row_Number() Over(Partition by PC.PC order by Disks.Disk_Desc) as N
and now it actually works 😀
Returns:
SGH-TGH-T61PAA8634T MUW095P SCSI CdRom DeviceMATSHITA DVD-RAM UJ-852ST9100821ASNULLNULL
So now I just need to understand what statement actually do.. ha ha
Thank you very much, now and can start building the rest of the "joins" into it... Super..
August 4, 2008 at 1:39 pm
good luck and thanks for the feedback.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 5, 2008 at 5:53 am
hi again rbarryyoung,
Hope you(or anybody) could help me the last time in this matter:
My script now looks like this,
With cteServers as (
Select PC.PC
, PC.PC_DomainName
, Drives.Drive
, Drives.Drive_Size
, Drive_FreeSpace
, Row_Number() Over(Partition by PC.PC order by Drives.Drive) as N
From PC
LEFT JOIN Drives
ON PC.ID_PC = Drives.ID_Drive
WHERE Drives.Drive_Type = 'Local')
, cteDisks as (
Select PC
,PC_DomainName
, Case N When 1 Then Drive Else NULL End as Disk1
, Case N When 1 Then Drive_Size Else NULL End as Drive_Size1
, Case N When 1 Then Drive_FreeSpace Else NULL End as Drive_FreeSpace1
, Case N When 2 Then Drive Else NULL End as Disk2
, Case N When 2 Then Drive_Size Else NULL End as Drive_Size2
, Case N When 2 Then Drive_FreeSpace Else NULL End as Drive_FreeSpace2
, Case N When 3 Then Drive Else NULL End as Disk3
, Case N When 3 Then Drive_Size Else NULL End as Drive_Size3
, Case N When 3 Then Drive_FreeSpace Else NULL End as Drive_FreeSpace3
, N
From cteServers)
Select PC
,PC_DomainName
, Max(Disk1) as Disk1
, Max(Drive_Size1) as Drive_Size1
, Max(Drive_FreeSpace1) as Drive_FreeSpace1
, Max(Disk2) as Disk2
, Max(Drive_Size2) as Drive_Size2
, Max(Drive_FreeSpace2) as Drive_FreeSpace2
, Max(Disk3) as Disk3
, Max(Drive_Size3) as Drive_Size3
, Max(Drive_FreeSpace3) as Drive_FreeSpace3
From cteDisks
Group by PC,PC_DomainName
And it works 😀
But i hoped to use it agains sql2000 through Managment studio, but no.. :crying:
It seems that there are different ways to "convert" this function, could you assist my, according to my working script.?
/T
August 5, 2008 at 9:46 am
Eliminating your WITH statements should be possible by just changing to temp tables from cte's, by replacing each WITH statement with it's INSERT INTO equivalent (INSERT INTO #ctedisks, for example). You can then eliminate the parentheses around the SELECT query.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 5, 2008 at 11:10 am
the problem is the Row_Number() function, its not supported in sql2000, can I eliminate that?
/T
August 5, 2008 at 11:17 am
Yes. You can use a "tally table", aka "Auxiliary Table of Numbers". Look up posts from Jeff Moden on creating a tally table. Joining to the tally table can be done but it does require a fair bit of extra work. I don't recall the exact technique, but chances are someone here can figure it out. I'm fairly new to the tally table concept, and I love it's power. I'm just not sufficiently familiar with the many ways to use it.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 5, 2008 at 1:43 pm
OK, try this:
Select TOP 99999999 PC.PC
, PC.PC_DomainName
, Drives.Drive
, Drives.Drive_Size
, Drive_FreeSpace
, Identity(int) as ID
, Cast(0 as int) as N
Into #Servers
From PC
LEFT JOIN Drives
ON PC.ID_PC = Drives.ID_Drive
WHERE Drives.Drive_Type = 'Local'
Order by Drives.Drive
Update s
Set N = s.ID + 1 - sg.MinID
From #Servers s
Join (Select ID, Min(ID) as MinID From #Servers Group By PC) sg
ON s.ID = sg.ID
--====
Select PC
,PC_DomainName
, Max(Disk1) as Disk1
, Max(Drive_Size1) as Drive_Size1
, Max(Drive_FreeSpace1) as Drive_FreeSpace1
, Max(Disk2) as Disk2
, Max(Drive_Size2) as Drive_Size2
, Max(Drive_FreeSpace2) as Drive_FreeSpace2
, Max(Disk3) as Disk3
, Max(Drive_Size3) as Drive_Size3
, Max(Drive_FreeSpace3) as Drive_FreeSpace3
From (Select PC
,PC_DomainName
, Case N When 1 Then Drive Else NULL End as Disk1
, Case N When 1 Then Drive_Size Else NULL End as Drive_Size1
, Case N When 1 Then Drive_FreeSpace Else NULL End as Drive_FreeSpace1
, Case N When 2 Then Drive Else NULL End as Disk2
, Case N When 2 Then Drive_Size Else NULL End as Drive_Size2
, Case N When 2 Then Drive_FreeSpace Else NULL End as Drive_FreeSpace2
, Case N When 3 Then Drive Else NULL End as Disk3
, Case N When 3 Then Drive_Size Else NULL End as Drive_Size3
, Case N When 3 Then Drive_FreeSpace Else NULL End as Drive_FreeSpace3
, N
From #Servers) as Disks
Group by PC,PC_DomainName
drop table #Servers
go
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 6, 2008 at 2:08 am
Hi Again,
And sorry for all the ? but this is by far the most complex SQL I have ever seen, but still learning 😀
after converted to the real table names, of the SQL2000 HP SIM DB, and changed a few bugs, I get only one row pr. server, so that nice, but only the first logical disk is shown, and its random.
any ideas, almost there 😀
Select TOP 99999999 devices.Name
, CIM_LogicalDisk.Description
, CIM_LogicalDisk.WIN32_Size
, CIM_LogicalDisk.dc_SpaceUsed
, Identity(int) as ID
, Cast(0 as int) as N
Into #Serverstemp5
From devices
LEFT JOIN CIM_LogicalDisk
ON devices.DeviceKey = CIM_LogicalDisk.NodeID
WHERE devices.ProductTypeStr = 'Server'
Order by CIM_LogicalDisk.Description
Update s
Set N = s.ID + 1 - sg.MinID
From #Serverstemp5 s
Join (Select ID, Min(ID) as MinID From #Serverstemp5 Group By ID) sg
ON s.ID = sg.ID
--====
Select Name
, Max(Description1) as Description1
, Max(Drive_Size1) as Drive_Size1
, Max(dc_SpaceUsed1) as dc_SpaceUsed1
, Max(Description2) as Description2
, Max(Drive_Size2) as Drive_Size2
, Max(dc_SpaceUsed2) as dc_SpaceUsed2
, Max(Description3) as Description3
, Max(Drive_Size3) as Drive_Size3
, Max(dc_SpaceUsed3) as dc_SpaceUsed3
From (Select Name
, Case N When 1 Then Description Else NULL End as Description1
, Case N When 1 Then WIN32_Size Else NULL End as Drive_Size1
, Case N When 1 Then dc_SpaceUsed Else NULL End as dc_SpaceUsed1
, Case N When 2 Then Description Else NULL End as Description2
, Case N When 2 Then WIN32_Size Else NULL End as Drive_Size2
, Case N When 2 Then dc_SpaceUsed Else NULL End as dc_SpaceUsed2
, Case N When 3 Then Description Else NULL End as Description3
, Case N When 3 Then WIN32_Size Else NULL End as Drive_Size3
, Case N When 3 Then dc_SpaceUsed Else NULL End as dc_SpaceUsed3
, N
From #Serverstemp5) as Disks
Group by Name
drop table #Serverstemp5
go
ex. from output
srvctxsrv01 C:\ [:NTFS]7336361984011447304192NULLNULLNULLNULLNULLNULL
srvdcfp01 NULLNULLNULLNULLNULLNULLNULLNULLNULL
srvcrm01 E:\ [sqldata:NTFS]29356143411213629390848NULLNULLNULLNULLNULLNULL
August 6, 2008 at 6:30 am
When you adapt the code to your table defs (which I haven't seen), you need to make sure that you keep the name changes straight. Try this:
Update s
Set N = s.ID + 1 - sg.MinID
From #Serverstemp5 s
Join (Select devices.name, ID, Min(ID) as MinID From #Serverstemp5 Group By devices.name) sg
ON s.ID = sg.ID
[/code]
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply