SQL Statement Issue - Combined Rows

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

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Statement Issue - Combined Rows

Author

Topic

nhess80
Yak Posting Veteran

83 Posts

Posted - 2014-08-28 : 14:08:36

Currently the sql statement I have below will return these results

SysName | Port | TxRx | Power

Henning - VZW, 1, Tx, -2.97
Henning - VZW, 1, Rx, -13.93
Henning - VZW, 2, Tx, -4.99
Henning - VZW, 2, Rx, -5.72

But I am trying to get it to return the results like this and cannot figure out how

Henning - VZW, 1, TX -2.9, RX -13.93
Henning - VZW, 2, TX -4.99, RX -5.72

Any help would be great..Thanks, Nick

SELECT SysName, RowID AS Port,c.UniqueName AS TxRx, ROUND( LOG10(RawStatus /10000) * 10, 2 ) AS Power

FROM [SolarWindsOrion].[dbo].[CustomPollerStatus] d

RIGHT JOIN(

SELECT b.NodeID,b.SysName,b.CustomPollerID,b.CustomPollerAssignmentID,UniqueName
FROM [SolarWindsOrion].[dbo].[CustomPollers] c

RIGHT JOIN(

SELECT a.NodeID,a.SysName,CustomPollerID,CustomPollerAssignmentID
FROM [SolarWindsOrion].[dbo].[CustomPollerAssignment] b

RIGHT JOIN(

SELECT NodeID,SysName
FROM [SolarWindsOrion].[dbo].[Nodes] a
Where Vendor = 'Accedian'

) a

ON a.NodeID = b.NodeID
Where b.CustomPollerID IN ('CA5B0D5F-DAB6-4DC4-8F78-58715DF05F7A','C7F914E5-C590-4CE0-91DE-145EC826CD6D')

) b
ON c.CustomPollerID = b.CustomPollerID

) c
ON d.CustomPollerAssignmentID = c.CustomPollerAssignmentID

Where RawStatus <> '0' AND NodeID = '13'

Group by c.NodeID,c.SysName,c.CustomPollerID,c.CustomPollerAssignmentID,c.UniqueName,RawStatus,RowID

Order by SysName, RowID,UniqueName DESC,RawStatus

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-29 : 09:19:37

This article discusses how to do what you need, and various techniques to do it:
http://www.sqlservercentral.com/articles/T-SQL/63681/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources