CODEWITHSUNDEEP
sqlsql-serversql-server-2008
webdev.gk
webdev.gk

Reputation: 219

Combine results of two different records in one

I have an SQL data stored in a single table but with different type and I want to combine the result in one liner show in the example below. Anyone can give suggestion to this SQL query?

My SQL query to get this results:

SELECT NumValue, Label, Type
FROM (CustomPollerStatusTable
      INNER JOIN CustomPollers ON (CustomPollerStatusTable.PollerID = 
                                   CustomPollers.CustomPollerID)) 
INNER JOIN Nodes ON (CustomPollerStatusTable.NodeID = Nodes.NodeID)
WHERE ((Nodes.Caption = 'fqdn') AND 
       (CustomPollers.Type = 'Student Info') AND 
       (CustomPollerStatusTable.NumValue > 89) AND  
       (NOT (CustomPollerStatusTable.Label LIKE '%.snapshot%')) AND
       (NOT (CustomPollerStatusTable.Label LIKE '%aggr%')) AND
       (NOT (CustomPollerStatusTable.Label = '/vol/scratch/'))
      )


====================================
NumValue | Label     |    Type
====================================
90       | Student 1 | Student Info
10       | Student 1 | Student Class
====================================

The results that I would like to achieve:

========================================================================== 
NumValue.Info | NumValue.Class | Label     | Type.Info    | Type.Class
========================================================================== 
90            | 10             | Student 1 | Student Info | Student Class
==========================================================================

Upvotes: 2

Views: 73

Answers (2)

Pat
Pat

Reputation: 310

This should do it. Slightly different from nrathaus' answer. Obviously you can use as to change the column names to whatever you want.

select
    s1.NumValue,
    s2.NumValue,
    s1.Label,
    s1.Type,
    s2.Type
from Student s1 inner join Student s2
on s1.Label = s2.Label
where s1.Type like '%Info'
and s2.Type like '%Class'

EDIT: Now that you have posted your select statement I think this might not work. Can you post the table structure?

EDIT2: This might work.

INSERT INTO TempTable(
    NumValInfo,
    Label,
    TypeInfo)
SELECT
    c.NumValue,
    c.Label,
    p.Type
FROM (CustomPollerStatusTable c INNER JOIN CustomPollers p
ON (c.PollerID = p.CustomPollerID)) 
    INNER JOIN Nodes n
ON (c.NodeID = n.NodeID)
WHERE n.Caption = 'fqdn'
AND p.Type = 'Student Info'
AND c.NumValue > 89
AND NOT (c.Label LIKE '%.snapshot%')
AND NOT (c.Label LIKE '%aggr%')
AND NOT (c.Label = '/vol/scratch/')
AND p.Type like '%Info'

UPDATE TempTable set
    NumValClass = c.NumValue,
    TypeClass = p.Type
FROM (CustomPollerStatusTable c INNER JOIN CustomPollers p
ON (c.PollerID = p.CustomPollerID)) 
    INNER JOIN Nodes n
ON (c.NodeID = n.NodeID)
    INNER JOIN TempTable t
ON t.Label = c.Label
WHERE n.Caption = 'fqdn'
AND p.Type = 'Student Info'
AND c.NumValue > 89
AND NOT (c.Label LIKE '%.snapshot%')
AND NOT (c.Label LIKE '%aggr%')
AND NOT (c.Label = '/vol/scratch/')
AND p.Type like '%Class'

SELECT * FROM TempTable

Upvotes: 2

Noam Rathaus
Noam Rathaus

Reputation: 5598

I think this will do:

SELECT tblInfo.NumValue AS `NumValue.Info`, 
       tblClass.NumValue AS `NumValue.Class`,
       Table.Label AS Label,
       tblInfo.Type AS `Type.Info`,
       tblClass.Type AS `Type.Class`
FROM (Table)
LEFT JOIN Table AS tblInfo ON tblInfo.Label = Table.Label ON tblInfo.Type = 'Student Info'
LEFT JOIN Table AS tblClass ON tblClass.Label = Table.Label ON tblClass.Type = 'Student Class'
GROUP BY Table.Label

Upvotes: -1

Related Questions