CODEWITHSUNDEEP
sqlsql-server
Ryan Morris
Ryan Morris

Reputation: 1

Putting two rows from table into one

EDIT: Got it working. I created a second view to hold the pivots, i think that was confusing me. Then edited my join for the AmusementMeter table to:

left join AmusementMeter as am_in on m.MachineId = am_in.MachineId and am_in.[Type] = '+'
left join AmusementMeter as am_out on m.MachineId = am_out.MachineId and am_out.[Type] = '-'

Now It all comes back on one row.

Thank you for helping.

I'm having trouble with this query, using Sql Server 2012. I'm pulling from multiple tables and I'm having trouble with just one right now. For every Machine there are two AmusementMeter records in the AmesementMeter Table. I need to get the two records in the AmusementMeter table to come over as one so I don't have two rows for each MachineId in the returned output.

What I have:

 SELECT DISTINCT
        m.MachineId as 'Machine Id' ,
        m.Name as 'Machine Name',
        mc.Name as 'Machine Class',
        m.InstallDate as 'Setup Date',
        m.Serial as 'Machine Serial Number',
        m.[Description] as 'Machine Description',
        l.ID as 'Location Id',
        l.Name as 'Location Name',
        m.DateMovedToLocation as 'Date moved to location',
        m.Manufacturer,
        m.PurchaseDate as 'Purchase Date',
        m.PurchasePrice as 'Purchase Price',
        m.PurchaseFrom as 'Purchase From',
        m.CabinetSerial as 'Cabinet Serial Number',
        m.Color as 'Cabinet Color',
        m.BillValidatorDescription as 'Bill Validator Description',
        CASE WHEN am.[Type] = '+' THEN am.Reading END AS 'IN Meter Reading',
        CASE WHEN am.[Type] = '+' THEN am.Value END AS 'IN Meter Value',
        CASE WHEN am.[Type] = '+' THEN am.Meter END AS 'IN Meter Digits',
        CASE WHEN am.[Type] = '-' THEN am.Reading END AS 'IN Meter Reading',
        CASE WHEN am.[Type] = '-' THEN am.Value END AS 'IN Meter Value',
        CASE WHEN am.[Type] = '-' THEN am.Meter END AS 'IN Meter Digits',
        CASE WHEN Pvt.[1] IS NULL THEN '' ELSE Pvt.[1] END AS 'REDEMPTION', 
        CASE WHEN Pvt.[2] IS NULL THEN '' ELSE Pvt.[2] END AS 'MAX BET', 
        CASE WHEN Pvt.[3] IS NULL THEN '' ELSE Pvt.[3] END AS 'JACKPOT CAP', 
        CASE WHEN Pvt.[4] IS NULL THEN '' ELSE Pvt.[4] END AS '50 AND 100 STAT', 
        CASE WHEN Pvt.[5] IS NULL THEN '' ELSE Pvt.[5] END AS 'MUTHA GOOSE KEY', 
        CASE WHEN Pvt.[6] IS NULL THEN '' ELSE Pvt.[6] END AS 'LICENSE EXPIRATION YR',
        CASE WHEN Pvt.[7] IS NULL THEN '' ELSE Pvt.[7] END AS 'PERMIT NUMBER',
        CASE WHEN Pvt.[8] IS NULL THEN '' ELSE Pvt.[8] END AS 'GAGGLE VER 126',
        CASE WHEN Pvt.[9] IS NULL THEN '' ELSE Pvt.[9] END AS 'METER VAULT RIVETS ',
        CASE WHEN Pvt.[10] IS NULL THEN '' ELSE Pvt.[10] END AS 'ASSET ID TAG'
    FROM            
    (SELECT  
        mudfr.MachineId, mudfr.UDFieldId, mudfr.Value
    FROM            
        MachineUserDefinedFieldRelation AS mudfr INNER JOIN
        MachineUserDefinedField AS mudf ON mudfr.UDFieldId = mudf.FieldId) AS pv PIVOT 
        (Max(Value) FOR UDFieldId IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) AS Pvt JOIN 
        Machine AS m on m.MachineId = Pvt.MachineId  JOIN
        MachineClass as mc on m.CategoryID = mc.ID JOIN
        Location as l on m.LocationID = l.ID INNER JOIN
        AmusementMeter as am on am.MachineId = m.MachineId

Output(I cut out a lot, left in the problem columns): Won't let me post screenshot, sorry. I get something like this, where I have MachineId showing up twice. The other values are from the AmuesmentMeter table. I want it to only be shown once and were we have the null values the values to take there place.

550001  NULL    NULL    NULL    928204  0.25    6

550001  547563  0.25    6   NULL    NULL    NULL

What I want: (Expected output)

550001  547563  0.25    6   928204  0.25 6

Thank you for helping.

Upvotes: 0

Views: 56

Answers (2)

Ryan Morris
Ryan Morris

Reputation: 1

EDIT: Got it working. I created a second view to hold the pivots, i think that was confusing me. Then edited my join for the AmusementMeter table to:

left join AmusementMeter as am_in on m.MachineId = am_in.MachineId and am_in.[Type] = '+'
left join AmusementMeter as am_out on m.MachineId = am_out.MachineId and am_out.[Type] = '-'

Now It all comes back on one row.

Thank you for helping.

Upvotes: 0

mordack550
mordack550

Reputation: 501

You have to add a GROUP BY clause on m.MachineId and select the MAX(columnname) for each column you want to "combine".

No ISNULL function is needed since MAX ignores NULLS

Upvotes: 1

Related Questions