SQL Query - Multiple Joins return as Columns

Question

First off I'm terrible at SQL and this is proving that point. The end goal is I have a database (What's Up Gold) that I'm trying to export all the devices out of and getting a few key fields in the process. I'm able to get all the data I want with the query below. However, due to the joins, it's returning duplicate rows where it finds a match. Ideally, I'd want each unique item added as a column so I'm left with one row per device with as many columns as needed to get all the attributes even if it's null.

SELECT Device.sDisplayName, NetworkInterface.sNetworkAddress, NetworkInterface.sNetworkName, DeviceGroup.sGroupName, 
ActiveMonitorType.sMonitorTypeName, ActionPolicy.sPolicyName, Device.sNote , DeviceAttribute.sValue
FROM 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 
WHERE Device.bRemoved = 0 AND sMonitorTypename NOT LIKE '%Interface%' ORDER BY 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 from 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 from 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 from 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 from Discovery on Mon Sep 26 12:46:30 2011    CenturyLink - 1-888-345-4762 option 2

What I'd like to see is that for every unique item in sValue column it would be returned as sValue1 sValue2 etc. That way I'm left with a single row per unique network device which is made unique by 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 from 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 to minimal Select

    SELECT d.sDisplayName
        , ni.sNetworkAddress
        , ni.sNetworkName
        , d.sNote 
        , da.sValue
    FROM NetworkInterface ni
    INNER join Device d ON ni.nDeviceID = d.nDeviceID 
        AND 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 
            AND amt.sMonitorTypename NOT LIKE '%Interface%' 
    left outer join ActionPolicy ap ON ap.nActionPolicyID = d.nActionPolicyID
    left outer JOIN DeviceAttribute da ON da.nDeviceID = d.nDeviceID
    ORDER BY ni.sNetworkName

Updated Query with Pivot

        SELECT d.sDisplayName
            , ni.sNetworkAddress
            , ni.sNetworkName
            , d.sNote 
            , da.sValue
        INTO #TEMP
        FROM NetworkInterface ni
        INNER join Device d ON ni.nDeviceID = d.nDeviceID 
            AND 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 
                AND amt.sMonitorTypename NOT LIKE '%Interface%' 
        left outer join ActionPolicy ap ON ap.nActionPolicyID = d.nActionPolicyID
        left outer JOIN DeviceAttribute da ON da.nDeviceID = d.nDeviceID


    DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

    SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.sValue) 
                FROM #TEMP c
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')

    set @query = 'SELECT sDisplayName, sNetworkAddress, sNote' + @cols + ' from 
                (
                    SELECT 
                        DisplayName
                        ,sNetworkAddress
                        ,sNetworkName
                        ,sNote 
                        ,sValue
                    from temp
               ) x
                pivot 
                (
                     max(sNote)
                    for sValue in (' + @cols + ')
                ) p '


    execute(@query)

    drop table #TEMP

Returns Error:

    (2195 row(s) affected)
    Msg 1038, Level 15, State 4, Line 11
    An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
    Msg 1038, Level 15, State 4, Line 53
    An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

@JohnHC is correct this is MS-SQL Server on the backend. I have updated tags accordingly. — rsaturns, Mar 28 '17 at 14:51
@Rahul the `dbo.` appears in the query in SSMS when dragging the table into the query window. — JohnHC, Mar 28 '17 at 14:52
@rsaturns do you know how many records will be returned per item? — Eli, Mar 28 '17 at 15:01
Looking at the results seems to range up to 8 values and as low as none. — rsaturns, Mar 28 '17 at 15:01
One other thing I would add is that when working with SQL, think about your data in sets, then join the sets together to either reduce or enlarge your ultimate data. If you look at how each set interacts with the JOINed set, you'll minimize your chances of accidental data. — Shawn, Mar 28 '17 at 16:39
> "`Ideally, I'd want each unique item added as a column so I'm left with one row per device with as many columns as needed`" The SQL language does **NOT** do dynamic columns. There is _no provision for this at all_ outside of dynamic sql (sql that write and executes more sql in a second step), which is usually a bad idea. You **must** be able to determine your output columns at query compile time. The closest you'll get is the `PIVOT` keyword, but that still wants to know about your output columns at compile time. You're best chance here is to do the pivot in your client. — Joel Coehoorn, Apr 06 '17 at 02:37

score 0 · Answer 1 · edited May 23 '17 at 11:54

0

What you need here is a pivot table.

If each item has the same amount of records returned in your query, or if you are aware of the max possible, you can get away with a simple pivot table. However, if you have an unknown max amount of records, you would need to resort to a dynamic pivot table.

Here's a link to another question on this site, which shows you how to create a dynamic pivot table: SQL Server dynamic PIVOT query?

SELECT d.sDisplayName
    , ni.sNetworkAddress
    , ni.sNetworkName
    , d.sNote 
    , da.sValue
INTO #TEMP
FROM NetworkInterface ni
INNER join Device d ON ni.nDeviceID = d.nDeviceID 
    AND 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 
        AND amt.sMonitorTypename NOT LIKE '%Interface%' 
left outer join ActionPolicy ap ON ap.nActionPolicyID = d.nActionPolicyID
left outer JOIN DeviceAttribute da ON da.nDeviceID = d.nDeviceID


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.sValue) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT sDisplayName, sNetworkAddress, sNote' + @cols + ' from 
            (
                SELECT 
                    DisplayName
                    ,sNetworkAddress
                    ,sNetworkName
                    ,sNote 
                    ,sValue
                from temp
           ) x
            pivot 
            (
                 max(sNote)
                for sValue in (' + @cols + ')
            ) p '


execute(@query)

drop table temp

edited May 23 '17 at 11:54

Community

1
1

answered Mar 28 '17 at 15:08

Eli

2,538
1
25
36

Let me see what I can come up with. You're correct a Pivot table will yield the desired result. I'm going to see how much a struggle to create it. I'll post back with edits as I make progress. Thanks. – rsaturns Mar 28 '17 at 15:12
I understand the premise of the dynamic pivot query. However, I can't seem to follow how you map data from your select query into the temp table. Needless to say, I'm well out of my depth here. – rsaturns Mar 28 '17 at 15:55
@rsaturns please see my edited answer - I stuck your query into that sample. – Eli Mar 28 '17 at 16:09
So using your edit it looks like it runs but I get no output, just the following: (2195 row(s) affected) Msg 208, Level 16, State 1, Line 23 Invalid object name 'temp'. I'm guessing the invalid object name temp is at the very end it should be `drop table #TEMP` however that still doesn't seem to work. – rsaturns Mar 28 '17 at 16:55
Now getting: (2195 row(s) affected) Msg 1038, Level 15, State 4, Line 11 An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name. Msg 1038, Level 15, State 4, Line 53 An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name. – rsaturns Mar 28 '17 at 16:58
@rsaturns Can you please try to run just the top portion of the query (until the declare statement)? Let's try to get that part working well first. – Eli Mar 29 '17 at 14:54
executing the top half just returns the following with no actual data: `(2195 row(s) affected)` – rsaturns Mar 29 '17 at 14:59
Ok, that's a good start. Does the following query work _after_ you run the first part of the query: `SELECT * FROM #temp` – Eli Mar 29 '17 at 15:45
Ok, now try to run all up to the: `execute(@query)` If that works fine, then the issue is with the query itself and we can look at it then. If the final query is the issue, please do the following: `SELECT @query;` and post the resulting query here. – Eli Mar 29 '17 at 17:27
So when I only have the Select, Declare, `set @query` sections and then execute. If I `select @query` I get a NULL result. I'm dropping this section: SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.sValue) FROM temp c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') – rsaturns Mar 29 '17 at 17:48
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/139408/discussion-between-eli-and-rsaturns). – Eli Mar 29 '17 at 18:54

Shawn · Answer 2 · 2017-04-06T02:22:56.400

It appears that you have multiple DeviceAttribute.sValues associated with your devices. So your duplicate rows are additional rows associated with your devices.

A few suggestions/notes:

1) To make it easier to read, alias your tables.

2) Keep consistent in your JOIN conditions. Some are ON x=y and some are ON y=x. It'll be easier to see what you're actually JOINing.

3) Move your WHERE conditions up into the JOIN they're associated with. You can get inconsistent results if you filter in the WHERE vs in the JOIN.

4) Doublecheck the JOIN groups for PivotDeviceToGroup/DeviceGroup and PivotActiveMonitorTypeToDevice/ActiveMonitorType. Are they returning what you expect them to return? Since both are double LEFT JOINs, you'll return rows in pdtg that don't match a dg and rows in pamtd that are amt LIKE '%Interface%', with amt data NULL.

Example:

SELECT d.sDisplayName
    , ni.sNetworkAddress
    , ni.sNetworkName
    , dg.sGroupName
    , amt.sMonitorTypeName
    , ap.sPolicyName
    , d.sNote 
    , da.sValue
FROM NetworkInterface ni
INNER join Device d ON ni.nDeviceID = d.nDeviceID 
    AND d.bRemoved = 0
-- left outer join dbo.DeviceType dt on dt.nDeviceTypeID = d.nDeviceTypeID  --- <<< You aren't using this table. Can you remove the JOIN?
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 
        AND amt.sMonitorTypename NOT LIKE '%Interface%' 
left outer join ActionPolicy ap ON ap.nActionPolicyID = d.nActionPolicyID
left outer JOIN DeviceAttribute da ON da.nDeviceID = d.nDeviceID

ORDER BY ni.sNetworkName

EDIT: This got to be a pretty complex PIVOT pretty quickly. But it was fun to play with. I don't have any data to test with, but see if something like this works.

 DECLARE @colNames nvarchar(max)
  , @query nvarchar(max)
 DECLARE @tq1 TABLE ( 
    sDisplayName varchar(max)
    , sNetworkAddress varchar(max)
    , sNetworkName varchar(max)
    , sGroupName varchar(max)
    , sMonitorType varchar(max)
    , sPolicyName varchar(max)
    , sNote varchar(max)
    , sValue varchar(max)
  )

 INSERT INTO @tq1 
 SELECT d.sDisplayName
  , ni.sNetworkAddress
  , ni.sNetworkName
  , dg.sGroupName
  , amt.sMonitorType
  , ap.sPolicyName
  , d.sNote 
  , da.sValue
 FROM dbo.Device d
 INNER JOIN dbo.NetworkInterface ni ON ni.nDeviceID = d.nDeviceID    
 LEFT OUTER JOIN dbo.PivotDeviceToGroup pdg ON pdg.nDeviceID = d.nDeviceID
  INNER JOIN dbo.DeviceGroup dg ON dg.nDeviceGroupID = pdg.nDeviceGroupID 
 LEFT OUTER JOIN dbo.PivotActiveMonitorTypeToDevice pamtd ON  pamtd.nDeviceID = d.nDeviceID 
  INNER join dbo.ActiveMonitorType amt on amt.nActiveMonitorTypeID = pamtd.nActiveMonitorTypeID
   AND amt.sMonitorTypename NOT LIKE '%Interface%'
 LEFT OUTER JOIN dbo.ActionPolicy ap ON ap.nActionPolicyID = d.nActionPolicyID
 LEFT JOIN DeviceAttribute da ON d.nDeviceID = da.nDeviceID 
 WHERE d.bRemoved = 0

 SELECT @colNames = STUFF( (SELECT DISTINCT ',' + QUOTENAME(sValue) FROM @tq1 FOR XML PATH(''), TYPE ).value('.','nvarchar(max)'),1,1,'' )
 SET @query = 'SELECT * FROM 
   (
    SELECT sDisplayName
     , sNetworkAddress
     , sNetworkName
     , sGroupName
     , sMonitorType
     , sPolicyName
     , sNote 
     , sValue
    FROM @tq1
   ) t1
   PIVOT
   (
    max(sValue) FOR sValue IN (' + @colNames + ')
   ) p 
   ORDER BY sNetworkName
   '
 EXECUTE ( @query )

So your query works with the removal of `-- left outer join dbo.DeviceType dt on dt.nDeviceTypeID = d.nDeviceTypeID --- <<< You aren't using this table. Can you remove the JOIN?`. You are also correct that there are multiple `DeviceAttribute.sValue` associated to a single device. Thus why multiple rows are returned. It does look like a dynamic pivot table would account for this by moving the multiple `DeviceAttribute.sValue` into unique column headers. But I can't seem to figure that out yet. — rsaturns, Mar 28 '17 at 15:40
Oops. Didn't see your comment about multiple columns for sValue. So yes, a Pivot Table would work. — Shawn, Mar 28 '17 at 15:40
Do you need the values for sValue? Will you be using those in an output? I would only query for the columns and the tables that you will ultimately consume in your end result. Anything else can become confusing noise. — Shawn, Mar 28 '17 at 15:44
I will need the data contained in the sValue. Unfortunately, this is a prime example of why free form database entry is a terrible idea. People were inconsistent in what data they put in what field. So now I'm trying to parse this all into one row per element, clean up the data and ultimately feed that into a script as a CSV. — rsaturns, Mar 28 '17 at 15:46
Doing some playing, it seems that the Query Optimizer changes the plan when the join is in the AND vs a WHERE. With the WHERE, SQL changes the join to a LEFT OUTER JOIN a ..... INNER JOIN b .... which is what I think your intention was. With the LEFT OUTER JOIN a ON x=y AND a=b, it leaves it as a LEFT JOIN, which will produce more empty rows by the nature of the LEFT JOIN, but may not be what was originally intended. That said, I'd still stick with the AND in the JOIN, but change this to an LEFT / INNER JOIN if you don't intend to return unmatched rows in the first LEFT JOIN. — Shawn, Mar 28 '17 at 17:05

SQL Query - Multiple Joins return as Columns

Updated Query to minimal Select

Updated Query with Pivot

2 Answers2

ORDER BY ni.sNetworkName