Need help on a T-SQL query (MSSQL)

Question

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 28-Sep-16 17:24pm

Eswar Chandra Vidya Sagar S

Updated 28-Sep-16 23:26pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2016-09-28T23:26:00

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)[^]