MSSQL join query with Group by

Question

my table structure like this:

ID      SID Type        Description SN
82      372 PC          XX          1234ZZ
83      372 Monitor     YYY         2234ZZ
587     444 PC          BBB         2255XX  
588     444 Monitor     CCC         4512XC

i would like to create a VIEW to show all record in same row group by SID (Staff ID)

my sql as follows:

SELECT      DISTINCT a.SID, 
            CAST(b.Description AS NVARCHAR(100)) AS Name_PC, 
            CAST(b.SN AS NVARCHAR(100)) AS SN_PC, 
            CAST(c.Description AS NVARCHAR(100)) AS Name_Monitor, 
            CAST(c.SN AS NVARCHAR(100)) AS SN_Monitor, 
            dbo.StaffDB.DisplayName
FROM        dbo.IT_Equ AS a INNER JOIN
            dbo.StaffDB ON a.SID = dbo.StaffDB.SID LEFT OUTER JOIN
            dbo.IT_Equ AS b ON a.SID = b.SID AND b.Type = 'PC' LEFT OUTER JOIN
            dbo.IT_Equ AS c ON a.SID = c.SID AND c.Type = 'Monitor'
WHERE     (b.Description IS NOT NULL) AND (b.SN IS NOT NULL) 
            AND (c.Description IS NOT NULL) AND (c.SN IS NOT NULL)
GROUP BY    a.SID, CAST(b.Description AS NVARCHAR(100)), 
            CAST(b.SN AS NVARCHAR(100)), 
            CAST(c.Description AS NVARCHAR(100)), 
            CAST(c.SN AS NVARCHAR(100)), 
            StaffDB.DisplayName

the code works fine if staf only had one record for PC and monitor, it will show the following results:

SID Name_PC SN_PC   Name_Monitor    SN_Monitor  DisplayName
372 XX      1234ZZ  YYY             2234ZZ      Peter   
444 BBB     2255XX  CCC             4512XC      John

but if the staff had more than one PC record or monitor record, it will create duplicate records such as

original record in db:

ID      SID     Type        Description     SN  
106     476     PC          PC018           84TK5   
107     476     Monitor     LCD018          60P5D   
421     476     PC          PC220           85HYC   
422     476     Monitor     LCD220          51RMR   

the result like this:

SID     Name_PC     SN_PC       Name_Monitor    SN_Monitor      DisplayName
476     PC018       84TK5       LCD018          60P5D           Mary
476     PC018       84TK5       LCD220          51RMR           Mary
476     PC220       85HYC       LCD018          60P5D           Mary
476     PC220       85HYC       LCD220          51RMR           Mary

is it possible to enhance the query to become this ?

SID     Name_PC     SN_PC       Name_Monitor    SN_Monitor      DisplayName
476     PC018       84TK5       LCD018          60P5D           Mary
476     PC220       85HYC       LCD220          51RMR           Mary

thanks

Answer 1

Problem is in the data - you have to fix duplicates. Obviously 1 monitor belongs to 2 PCs -
PC018 - 60P5D and PC220 - 60P5D. Alternatively you can try to arrange them and somehow to take 1st SN for the 1st PC and so on, but I don't think this is the right way.

Answer 2

how to you want to determine which sn_pc you want to use?

A distinct will not solve your problem, a distinct only eliminate identical lines.

You have to aggregate if it's possible : min(sn_pc) You can use view function : row_number over (partition by sid, name_pc order by sn_pc) in a subquery then where rownum = 1

But when reading again the results, it seems you are missing a join criteria when joining your tables

dbo.IT_Equ AS c ON a.SID = c.SID AND c.Type = 'Monitor'

Here is the problem, use a subquery to only select the last line if possible.