sqlsql-server

How to aggregate two rows returned by SQL query into one?


This is how I setup my test database:

-- Computer assets
CREATE TABLE tblAssets 
(
    AssetID int NOT NULL,
    AssetName nvarchar(200) NOT NULL,
    UserName nvarchar(150),
    PRIMARY KEY (AssetID)
);

-- Software titles
CREATE TABLE tblSoftwareUNI 
(
    SoftID int NOT NULL,
    softwareName nvarchar(300),
    PRIMARY KEY (SoftID)
);

-- Software installed on computer assets
CREATE TABLE tblSoftware 
(
    SoftwareID int NOT NULL,
    AssetID int NOT NULL,
    SoftID int NOT NULL,
    SoftwareVersion nvarchar(100),
    PRIMARY KEY (SoftwareID),
    CONSTRAINT FK_tblSoftware_tblAssets 
        FOREIGN KEY (AssetID)
        REFERENCES tblAssets(AssetID),
    CONSTRAINT FK_tblSoftware_tblSoftwareUNI 
        FOREIGN KEY (SoftID)
        REFERENCES tblSoftwareUNI(SoftID)
);

-- File existence on asset
CREATE TABLE tblFileVersions 
(
    VersionID int NOT NULL,
    AssetID int NOT NULL,
    FilePathfull nvarchar(1000),
    Found bit,
    PRIMARY KEY (VersionID),
    CONSTRAINT FK_tblFileVersions_tblAssets 
        FOREIGN KEY (AssetID)
        REFERENCES tblAssets(AssetID)  
);

INSERT INTO tblSoftwareUNI (SoftID, softwareName) VALUES (1, 'Adobe Acrobat');
INSERT INTO tblSoftwareUNI (SoftID, softwareName) VALUES (2, 'Microsoft Word');

INSERT INTO tblAssets (AssetID, AssetName, UserName) VALUES (1, 'PC1', 'Vito Corleone');
INSERT INTO tblAssets (AssetID, AssetName, UserName) VALUES (2, 'PC2', 'Jack Sparrow');
INSERT INTO tblAssets (AssetID, AssetName, UserName) VALUES (3, 'PC3', 'Han Solo');
INSERT INTO tblAssets (AssetID, AssetName, UserName) VALUES (4, 'PC4', 'Anakin Skywalker');
INSERT INTO tblAssets (AssetID, AssetName, UserName) VALUES (5, 'PC5', 'Harry Potter');

INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (1, 1, 1, '10.9');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (2, 2, 1, '11.0');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (3, 2, 2, '1.42');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (4, 2, 2, '2.57');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (5, 3, 1, '11.0');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (6, 3, 2, '2.57');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (7, 4, 1, '10.9');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (8, 4, 2, '1.40.9');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (9, 5, 1, '11.0');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (10, 5, 2, '1.40.9');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (11, 5, 2, '2.57');

INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (1, 1, 'c:\temp\temp.txt', 0);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (2, 1, 'c:\test\testfile.txt', 0);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (3, 2, 'c:\temp\temp.txt', 1);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (4, 2, 'c:\test\testfile.txt', 1);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (5, 3, 'c:\temp\temp.txt', 0);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (6, 3, 'c:\test\testfile.txt', 1);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (7, 4, 'c:\temp\temp.txt', 1);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (8, 4, 'c:\test\testfile.txt', 0);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (9, 5, 'c:\temp\temp.txt', 1);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (10, 5, 'c:\test\testfile.txt', 1);

I run this query:

SELECT
    tblAssets.AssetName,
    tblAssets.UserName,
    CASE LEFT(tblSoftware.SoftwareVersion, 1)
        WHEN '1' THEN tblSoftware.SoftwareVersion
        ELSE ''
    END AS v1,
    CASE LEFT(tblSoftware.SoftwareVersion, 1)
        WHEN '2' THEN tblSoftware.SoftwareVersion
        ELSE ''
    END AS v2,
    (SELECT TOP 1 tblFileVersions.Found 
     FROM tblFileVersions 
     WHERE tblFileVersions.FilePathFull = 'c:\test\testfile.txt' 
       AND tblFileVersions.AssetID = tblSoftware.AssetID) FileFound
FROM 
    tblSoftware
INNER JOIN 
    tblAssets ON tblSoftware.AssetID = tblAssets.AssetID
INNER JOIN 
    tblSoftwareUNI ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE
    tblSoftwareUni.softwareName LIKE 'Microsoft%'

Which returns:

AssetName UserName v1 v2 FileFound
PC2 Jack Sparrow 1.42 1
PC2 Jack Sparrow 2.57 1
PC3 Han Solo 2.57 1
PC4 Anakin Skywalker 1.40.9 0
PC5 Harry Potter 1.40.9 1

I would like to have only 1 line for each asset, so like this:

AssetName UserName v1 v2 FileFound
PC2 Jack Sparrow 1.42 2.57 1
PC3 Han Solo 2.57 1
PC4 Anakin Skywalker 1.40.9 0
PC5 Harry Potter 1.40.9 1

I cannot get my query results to my satisfaction. Can you help me?


Solution

  • This is achievable using left joins.

      Select  t1.AssetName,
          t1.UserName, t.SoftwareVersion, t4.SoftwareVersion,
            (select top 1 tblFileVersions.Found from tblFileVersions where tblFileVersions.FilePathFull = 'c:\test\testfile.txt' And tblFileVersions.AssetID = t1.AssetID) FileFound
        FROM tblAssets t1
        
        LEFT JOIN 
          (select t2.AssetId, t3.SoftID, t2.SoftwareVersion from tblSoftware t2 
          inner join  tblSoftwareUNI t3 on t3.SoftID  = t2.SoftID 
          where t3.softwareName Like 'Microsoft%' and Left(t2.SoftwareVersion, 1)='1') t
        on t.AssetId = t1.AssetId
        LEFT JOIN 
          (select t2.AssetId, t3.SoftID, t2.SoftwareVersion from tblSoftware t2 
          inner join  tblSoftwareUNI t3 on t3.SoftID  = t2.SoftID 
          where t3.softwareName Like 'Microsoft%' and Left(t2.SoftwareVersion, 1)='2') t4
        on t1.AssetId = t4.AssetId
        WHERE isnull(t.SoftwareVersion,'') != '' or isnull(t4.SoftwareVersion, '') != ''
    

    see dbfiddle