calculation based on columns in row

Question

Hi,

Could you please help me with the below result?
I have the below table and looking for the below result.

create table test(Name1 varchar(10),Name2 varchar(10),Color varchar(10),ID_Test float)

insert into test
(' ',' ','Brown',' ')
,(' ',' ','Red',1)
,('Alice',' ',' ',' ')
,('Fred','Bron ',' ',' ')
,('Fred','Rose',' ',111)
,('Pop','Bin ',' ',10)
,('Blace','Bin ',' ',0)

result:
value1= #distinct each column
valu2= # blank for each coulmn/total records

I need to have the below result:

column_name, value1, value2

Name1, 5, 0.28 --- means 2/7=0.28
Name2, 4, 0.42 ---means 3/7=0.42
Color, 3, 0.71 --- means 5/7=0.71
ID_Test, 5, 0.42 --- means 3/7=0.42

Answer 1

Hi @Zaran ,

Please try the following. Thanks!

CREATE TABLE test(Name1 VARCHAR(10),Name2 VARCHAR(10), Color VARCHAR(10), ID_Test VARCHAR(10))  
      
INSERT INTO test VALUES  
(' ',' ','Brown',' ')  
,(' ',' ','Red','1')  
,('Alice',' ',' ',' ')  
,('Fred','Bron ',' ',' ')  
,('Fred','Rose',' ','111')  
,('Pop','Bin ',' ','10')  
,('Blace','Bin ',' ','0')  
  
  
DECLARE @ColumnTable TABLE (id INT IDENTITY, column_name VARCHAR(100))  
DECLARE @DestinationTable TABLE (id INT IDENTITY, column_name VARCHAR(100), value1 INT, value2 DECIMAL(10,2))  
  
INSERT INTO @ColumnTable (column_name)  
SELECT COLUMN_NAME  
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME = 'test'      

DECLARE @columnName VARCHAR(100)  
DECLARE @id AS INT  
DECLARE @sqlText VARCHAR(1000);       
  
WHILE EXISTS (SELECT * FROM @ColumnTable)  
 BEGIN   
 SELECT TOP 1 @columnName =  column_name, @id = id FROM @ColumnTable  
 SET @sqlText = 'SELECT '''+ @columnName + ''' AS column_name, COUNT(DISTINCT ' + @columnName + ' ) AS value1, CAST(SUM(CASE WHEN ' + @columnName +   
' = NULL OR TRIM( '+ @columnName + ' ) = '''' THEN 1 ELSE 0 END) * 1.0 / COUNT(*)  AS DECIMAL(10,2)) AS value2 FROM test'  
  
 INSERT INTO @DestinationTable  
 Exec (@sqlText)  
     DELETE FROM @ColumnTable WHERE id = @id  
 END  
  
SELECT * FROM @DestinationTable  

Answer 2

Hi @Zaran ,

Would you please try the following. Thanks!

Declare @test Table(Name1 varchar(10),Name2 varchar(10),Color varchar(10),ID_Test varchar(10))  
  
insert into @test Values  
(' ',' ','Brown',' ')  
,(' ',' ','Red','1')  
,('Alice',' ',' ',' ')  
,('Fred','Bron ',' ',' ')  
,('Fred','Rose',' ','111')  
,('Pop','Bin ',' ','10')  
,('Blace','Bin ',' ','0')  
  
  
SELECT '1' AS serial_no, 'Name1' AS column_name, COUNT(DISTINCT Name1) AS value1, CAST(SUM(CASE WHEN Name1 = NULL OR TRIM(Name1) = '' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS DECIMAL(10,2)) AS value2 FROM @test  
UNION  
SELECT '2' AS serial_no, 'Name2' AS column_name, COUNT(DISTINCT Name2) AS value1, CAST(SUM(CASE WHEN Name2 = NULL OR TRIM(Name2) = '' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS DECIMAL(10,2)) AS value2 FROM @test  
UNION  
SELECT '3' AS serial_no, 'Color' AS column_name, COUNT(DISTINCT Color) AS value1, CAST(SUM(CASE WHEN Color = NULL OR TRIM(Color) = '' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS DECIMAL(10,2)) AS value2 FROM @test  
UNION  
SELECT '4' AS serial_no, 'ID_Test' AS column_name, COUNT(DISTINCT ID_Test) AS value1, CAST(SUM(CASE WHEN ID_Test = NULL OR TRIM(ID_Test) = '' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS DECIMAL(10,2)) AS value2 FROM @test  

----------

If the above response is helpful, please accept as answer and up-vote it. Thanks!

Answer 3

nasreen-akter has provided the latest answer, do you have any updates？

Echo