query assistance- rows to cols

Question

query assistance- rows to cols

benjz_mx

SSC Enthusiast

Points: 150
More actions
August 28, 2014 at 2:00 pm

#397274

Hi there,
i would like to reach out in converting rows to cols.
Format:
NodeName|upsBasicIdentName|upsBasicIdentModel | upsAdvIdentSerialNumber | upsAdvIdentDateOfManufacture | upsBasicBatteryLastReplaceDate
i have this query. the values of each are under status. thank you for your help!
SELECT
Nodes.Caption AS NodeName, CustomNodePollers_CustomPollers.MIB AS MIB, CustomNodePollerStatus_CustomPollerStatus.Status AS Status
FROM
((Nodes INNER JOIN CustomPollerAssignment CustomNodePollerAssignment_CustomPollerAssignment ON (Nodes.NodeID = CustomNodePollerAssignment_CustomPollerAssignment.NodeID)) INNER JOIN CustomPollers CustomNodePollers_CustomPollers ON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerID = CustomNodePollers_CustomPollers.CustomPollerID)) INNER JOIN CustomPollerStatus CustomNodePollerStatus_CustomPollerStatus ON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerAssignmentID = CustomNodePollerStatus_CustomPollerStatus.CustomPollerAssignmentID)
WHERE
(
(CustomNodePollers_CustomPollers.MIB like 'PowerNet-MIB:upsBasicIdentName') Or
(CustomNodePollers_CustomPollers.MIB like 'PowerNet-MIB:upsBasicIdentModel') Or
(CustomNodePollers_CustomPollers.MIB like 'PowerNet-MIB:upsAdvIdentSerialNumber') Or (CustomNodePollers_CustomPollers.MIB like 'PowerNet-MIB:upsAdvIdentDateOfManufacture') Or (CustomNodePollers_CustomPollers.MIB like 'PowerNet-MIB:upsBasicBatteryLastReplaceDate')
)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

You can use cross tabs to get the results as desired. You can read about them in here: http://www.sqlservercentral.com/articles/T-SQL/63681/

I changed your query to make it easier to read (for me) and give an example of what you need.

This time was an easy guess, but you should follow best practices when posting or you might not get nice answers in the future. You can read about them in the article linked in my signature.

SELECT n.Caption AS NodeName

,MAX( CASE WHEN cp.MIB = 'PowerNet-MIB:upsBasicIdentName' THEN cps.STATUS END) AS upsBasicIdentName

,MAX( CASE WHEN cp.MIB = 'PowerNet-MIB:upsBasicIdentModel' THEN cps.STATUS END) AS upsBasicIdentModel

,MAX( CASE WHEN cp.MIB = 'PowerNet-MIB:upsAdvIdentSerialNumber' THEN cps.STATUS END) AS upsAdvIdentSerialNumber

FROM Nodes n

JOIN CustomPollerAssignment cpa ON n.NodeID = cpa.NodeID

JOIN CustomPollers cp ON cpa.CustomPollerID = cp.CustomPollerID

JOIN CustomPollerStatus cps ON cpa.CustomPollerAssignmentID = cps.CustomPollerAssignmentID

WHERE cp.MIB IN( 'PowerNet-MIB:upsBasicIdentName'

,'PowerNet-MIB:upsBasicIdentModel'

,'PowerNet-MIB:upsAdvIdentSerialNumber'

,'PowerNet-MIB:upsAdvIdentDateOfManufacture'

,'PowerNet-MIB:upsBasicBatteryLastReplaceDate')

GROUP BY n.Caption

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 2

You can use the PIVOT command as well, but I'd recommend crosstabs, as Luis showed.

benjz_mx SSC Enthusiast Points: 150 More actions · Answer 3

thank you for your help and most importantly to your advise- lesson learned.