Click here to Skip to main content
15,905,563 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello Experts,

I have source data which somewhat looks like the below table..

MachineName ComplianceCheckItem Status
M1 C1 Pass
M1 C2 Pass
M1 C3 Fail
M1 C4 Pass
M2 C3 Pass
M2 C4 Pass
M3 C1 Pass
M3 C3 Fail
M3 C5 Pass

There are 5 compliance check items for which i need to have the status against each machine. The source may or may not have data corresponding to these machine + compliance check item combination. For all the compliance check items that are not availble in the source i want to show it up as 'N/A'. The output should be as shown below..

MachineName ComplianceCheckItem Status
M1 C1 Pass
M1 C2 Pass
M1 C3 Fail
M1 C4 Pass
M1 C5 N/A
M2 C1 N/A
M2 C2 N/A
M2 C3 Pass
M2 C4 Pass
M2 C5 N/A
M3 C1 Pass
M3 C2 N/A
M3 C3 Fail
M3 C4 N/A
M3 C5 Pass
Below is the master list of ComplianceCheckItems

ComplianceCheckItem
C1
C2
C3
C4
C5

What I have tried:

Tried to get distinct machine name and then cross join with distinct ComplianceCheckItem
but since the data being huge, i think that it is not the optimal solution..
Posted
Updated 28-Sep-16 23:26pm

1 solution

I'm not sure i understand you well, but it seems you want to cross join[^] data, which mean you want to get all combination of MachineName and ComplianceCheckItem. Check below example:

SQL
DECLARE @master TABLE (MachineName VARCHAR(50), ComplianceCheckItem VARCHAR(50), [Status] VARCHAR(50))

INSERT INTO @master (MachineName, ComplianceCheckItem, [Status])
VALUES('M1', 'C1', 'Pass'),
('M1', 'C2', 'Pass'),
('M1', 'C3', 'Fail'),
('M1', 'C4', 'Pass'),
('M2', 'C3', 'Pass'),
('M2', 'C4', 'Pass'),
('M3', 'C1', 'Pass'),
('M3', 'C3', 'Fail'),
('M3', 'C5', 'Pass')


DECLARE @cci TABLE (ComplianceCheckItem VARCHAR(50))
INSERT INTO @cci(ComplianceCheckItem)
VALUES('C1'), ('C2'), ('C3'), ('C4'), ('C5')

SELECT t.MachineName, t.ComplianceCheckItem, COALESCE(ma.[Status] , 'NA') AS [Status]
FROM (
	SELECT DISTINCT m.MachineName, c.ComplianceCheckItem 
	FROM @master AS m , @cci AS c
	) AS t LEFT JOIN @master AS ma ON t.MachineName = ma.MachineName AND t.ComplianceCheckItem = ma.ComplianceCheckItem 


Result (as expected):
MachineName	ComplianceCheckItem	Status
M1			C1					Pass
M1			C2					Pass
M1			C3					Fail
M1			C4					Pass
M1			C5					NA
M2			C1					NA
M2			C2					NA
M2			C3					Pass
M2			C4					Pass
M2			C5					NA
M3			C1					Pass
M3			C2					NA
M3			C3					Fail
M3			C4					NA
M3			C5					Pass


For further details, please see:
Visual Representation of SQL Joins[^]
Using Cross Joins[^]
COALESCE (Transact-SQL)[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900