sql server - SQL Query - Multiple Joins return as Columns -
first off i'm terrible @ sql , proving point. end goal have database (what's gold) i'm trying export devices out of , getting few key fields in process. i'm able data want query below. however, due joins, it's returning duplicate rows finds match. ideally, i'd want each unique item added column i'm left 1 row per device many columns needed attributes if it's null.
select device.sdisplayname, networkinterface.snetworkaddress, networkinterface.snetworkname, devicegroup.sgroupname, activemonitortype.smonitortypename, actionpolicy.spolicyname, device.snote , deviceattribute.svalue networkinterface join device on (networkinterface.ndeviceid = device.ndeviceid) left outer join dbo.devicetype on device.ndevicetypeid = devicetype.ndevicetypeid left outer join dbo.pivotdevicetogroup on pivotdevicetogroup.ndeviceid = device.ndeviceid left outer join dbo.devicegroup on devicegroup.ndevicegroupid = pivotdevicetogroup.ndevicegroupid left outer join dbo.pivotactivemonitortypetodevice on pivotactivemonitortypetodevice.ndeviceid = device.ndeviceid left outer join dbo.activemonitortype on activemonitortype.nactivemonitortypeid = pivotactivemonitortypetodevice.nactivemonitortypeid left outer join actionpolicy on device.nactionpolicyid = actionpolicy.nactionpolicyid left outer join deviceattribute on device.ndeviceid = deviceattribute.ndeviceid device.bremoved = 0 , smonitortypename not '%interface%' order snetworkname example returned data:
sdisplayname snetworkaddress sgroupname smonitortypename spolicyname snote svalue portland dci south 10.0.0.1 main wan map ping dci 10 gig alert added discovery on mon sep 26 12:46:30 2011 portland hq data center portland dci south 10.0.0.1 main wan map ping dci 10 gig alert added discovery on mon sep 26 12:46:30 2011 pdx data center 10 gig dci south portland dci south 10.0.0.1 main wan map ping dci 10 gig alert added discovery on mon sep 26 12:46:30 2011 eth10000-xxxxxxx portland dci south 10.0.0.1 main wan map ping dci 10 gig alert added discovery on mon sep 26 12:46:30 2011 centurylink - 1-888-345-4762 option 2 what i'd see every unique item in svalue column returned svalue1 svalue2 etc. way i'm left single row per unique network device made unique snetworkaddress field.
desired query output:
sdisplayname snetworkaddress sgroupname smonitortypename spolicyname snote svalue1 svalue2 svalue3 svalue4 portland dci south 10.0.0.1 main wan map ping dci 10 gig alert added discovery on mon sep 26 12:46:30 2011 portland hq data center pdx data center 10 gig dci south eth10000-xxxxxxx centurylink - 1-888-345-4762 option 2 updated query minimal select
select d.sdisplayname , ni.snetworkaddress , ni.snetworkname , d.snote , da.svalue networkinterface ni inner join device d on ni.ndeviceid = d.ndeviceid , d.bremoved = 0 left outer join dbo.pivotdevicetogroup pdg on pdg.ndeviceid = d.ndeviceid left outer join dbo.devicegroup dg on dg.ndevicegroupid = pdg.ndevicegroupid left outer join dbo.pivotactivemonitortypetodevice pamtd on pamtd.ndeviceid = d.ndeviceid left outer join dbo.activemonitortype amt on amt.nactivemonitortypeid = pamtd.nactivemonitortypeid , amt.smonitortypename not '%interface%' left outer join actionpolicy ap on ap.nactionpolicyid = d.nactionpolicyid left outer join deviceattribute da on da.ndeviceid = d.ndeviceid order ni.snetworkname updated query pivot
select d.sdisplayname , ni.snetworkaddress , ni.snetworkname , d.snote , da.svalue #temp networkinterface ni inner join device d on ni.ndeviceid = d.ndeviceid , d.bremoved = 0 left outer join dbo.pivotdevicetogroup pdg on pdg.ndeviceid = d.ndeviceid left outer join dbo.devicegroup dg on dg.ndevicegroupid = pdg.ndevicegroupid left outer join dbo.pivotactivemonitortypetodevice pamtd on pamtd.ndeviceid = d.ndeviceid left outer join dbo.activemonitortype amt on amt.nactivemonitortypeid = pamtd.nactivemonitortypeid , amt.smonitortypename not '%interface%' left outer join actionpolicy ap on ap.nactionpolicyid = d.nactionpolicyid left outer join deviceattribute da on da.ndeviceid = d.ndeviceid declare @cols nvarchar(max), @query nvarchar(max); set @cols = stuff((select distinct ',' + quotename(c.svalue) #temp c xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'') set @query = 'select sdisplayname, snetworkaddress, snote' + @cols + ' ( select displayname ,snetworkaddress ,snetworkname ,snote ,svalue temp ) x pivot ( max(snote) svalue in (' + @cols + ') ) p ' execute(@query) drop table #temp returns error:
(2195 row(s) affected) msg 1038, level 15, state 4, line 11 object or column name missing or empty. select statements, verify each column has name. other statements, empty alias names. aliases defined "" or [] not allowed. change alias valid name. msg 1038, level 15, state 4, line 53 object or column name missing or empty. select statements, verify each column has name. other statements, empty alias names. aliases defined "" or [] not allowed. change alias valid name.
what need here pivot table.
if each item has same amount of records returned in query, or if aware of max possible, can away simple pivot table. however, if have unknown max amount of records, need resort dynamic pivot table.
here's link question on site, shows how create dynamic pivot table: sql server dynamic pivot query?
select d.sdisplayname , ni.snetworkaddress , ni.snetworkname , d.snote , da.svalue #temp networkinterface ni inner join device d on ni.ndeviceid = d.ndeviceid , d.bremoved = 0 left outer join dbo.pivotdevicetogroup pdg on pdg.ndeviceid = d.ndeviceid left outer join dbo.devicegroup dg on dg.ndevicegroupid = pdg.ndevicegroupid left outer join dbo.pivotactivemonitortypetodevice pamtd on pamtd.ndeviceid = d.ndeviceid left outer join dbo.activemonitortype amt on amt.nactivemonitortypeid = pamtd.nactivemonitortypeid , amt.smonitortypename not '%interface%' left outer join actionpolicy ap on ap.nactionpolicyid = d.nactionpolicyid left outer join deviceattribute da on da.ndeviceid = d.ndeviceid declare @cols nvarchar(max), @query nvarchar(max); set @cols = stuff((select distinct ',' + quotename(c.svalue) temp c xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'') set @query = 'select sdisplayname, snetworkaddress, snote' + @cols + ' ( select displayname ,snetworkaddress ,snetworkname ,snote ,svalue temp ) x pivot ( max(snote) svalue in (' + @cols + ') ) p ' execute(@query) drop table temp
Comments
Post a Comment