Records Only present in Output when there is blank

Transact-SQL
1

Hello Techie,

may anyone please suggest me how to achieve this complicated scenario.

I am trying to find blank in the "value" column whose description is available i.e (Description Column should Not NULL or blank).

here in record number 2 value for Number Of Pieces is blank similarly

for record 3 value for design and Color is blank

Data:

DECLARE @TABLEA table
(

Part VARCHAR (50),
CLASS VARCHAR (50),
[DESCRIPTION 1] VARCHAR (50),
Value1 VARCHAR (50),
[DESCRIPTION 2] VARCHAR (50),
Value2 VARCHAR (50),
[DESCRIPTION 3] VARCHAR (50),
Value3 VARCHAR (50),
[DESCRIPTION 4] VARCHAR (50),
Value4 VARCHAR (50)
)

INSERT @TABLEA

SELECT 'AXHOP123', 'DS', 'Number Of Pieces', '5', 'Design', 'Logo' , 'Color', 'White', NULL , NULL UNION ALL
SELECT 'AXHOP123', 'DS', 'Number Of Pieces', '5', 'Design', 'Logo' , 'Color', 'White', NULL , '' UNION ALL
SELECT 'AYNP2589', 'DS', 'Number Of Pieces','', 'Design', 'No Logo', 'Color', 'Black', 'Material', 'Rubber' UNION ALL
SELECT 'AAA19-1508','NP', 'Number Of Pieces', '15', 'Design', '' , 'Color', '' , 'Material', 'Nylon' UNION ALL
SELECT 'AAA19-2405','SP', 'Number Of Pieces', '4', 'Design', 'No Logo', 'Color', 'Black', 'Material', 'Rubber' UNION ALL
SELECT 'AAA19-4003','NS', 'Number Of Pieces', '1', 'Design', 'No Logo', 'Color', 'Tan' , 'Material', 'Rubber'

Expected Output

Pasted image851×61 3.26 KB

Note: Description 4 and Value 4 is not shown in the Output, beacause value 4 is neither Null Or Blank for any of the NOT NULL description.

2

What if there are more than 2 blank in values but only 1 NOT NULL in description ?

3

Hi Mangal,

I just trying to see blank or null in value (value 1 , value 2 value 3 etc.) column whose description (description 1, description 2 etc.) is neither NULL or blank
if any of the description (description 1, description 2 ... etc is NULL or blannk then we eliminate it from output)

Thanks

4

Try this:

SELECT *

FROM @TABLEA
WHERE '' IN (Value1, Value2, Value3, Value4)
AND [DESCRIPTION 1] + [DESCRIPTION 2]+  [DESCRIPTION 3]+ [DESCRIPTION 4] IS NOT NULL

Thanks for providing the SQL scripts of data.

5

Thanks, but in output [description 4] is appearing even [value 4] is neither NULL or blank

6

So if value is not blank you don't want the related description in SELECT itself?

7

Yes Mangal. Select list contain description and its value where blank is present in Value feild for a particular description.