count rows based on column name

Question

Hi,

I have the below table. I want to take the below result.

Create table test(columnname1 nvarchar(255),columnname2 nvarchar(255),columnname3 nvarchar(255))

insert into test
values ('Pink','Red',Blue')
,(' ',' ','Brown')
,(' ',' ','Red')
,('Yellow',' ',' ')

I need to have the below result: here the calculations of value1 and value2
value1=select count(distinct columnname1 ) from test
value2=select count(columnname1 ) from test where columnname1 is null /select count(columnname1) from test

Name,value1,value2

columnname1, 2, 0.5 --(which means 2/4=0.5)
columnname2,1, 0.75 --(which means 3/4=0.75)
columnname3, 3, 0.25 --(which means 1/4=0.25)

Answer 1

Hi anonymous user,

You could add one CTE to convert all columns to the same data type first.

Please refer below query:

drop table if exists test  
      
 Create table test  
 (columnname1 nvarchar(255),  
 columnname2 nvarchar(255),  
 columnname3 nvarchar(255),  
 columnname4 float )  
      
 insert into test  
 values ('Pink','Red','Blue',1.0)  
 ,(' ',' ','Brown',NULL)  
 ,(' ',' ','Red',2.0)  
 ,('Yellow',' ',' ',NULL)  
    
;with cte as   
(select columnname1,columnname2,columnname3,cast(columnname4 as nvarchar(255)) as columnname4  from test)	     
 select name,[Y] as value1,cast((sum-[Y]) as float)/sum as value2  
   from  
   (  
     select Name,iif(Value='','N','Y') value,(select count(*) from test) sum  
     from cte  
     unpivot  
     (  
       Value  
       for Name in (columnname1, columnname2,  
                   columnname3,columnname4) --add new column name here  
     ) unp  
   ) src  
   pivot  
   (  
     count(value)  
     for value in ([Y], [N])  
   ) piv  

If above is still not working, please provide your sample data and expected output.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

One way

;With cteCount As
(Select Count(*) As RCount From test),
ctePivot As
(SELECT Name, ColumnValue, RCount
FROM test
Cross Join cteCount
UNPIVOT
(
    ColumnValue
    FOR Name in (columnname1, columnname2, columnname3)
) AS SchoolUnpivot
Where IsNull(ColumnValue, '') <> '')
Select Name, Cast(Count(ColumnValue) As float) / Max(RCount) 
From ctePivot
Group By Name
Order By Name;

Tom

Answer 3

Hi anonymous user,

Welcome to Microsoft Q&A!

Please refer below:

 select name,[Y] as value1,cast((sum-[Y]) as float)/sum as value2  
 from  
 (  
   select Name,iif(Value='','N','Y') value,(select count(*) from test) sum  
   from test  
   unpivot  
   (  
     Value  
     for Name in (columnname1, columnname2,  
                 columnname3)  
   ) unp  
 ) src  
 pivot  
 (  
   count(value)  
   for value in ([Y], [N])  
 ) piv  

Output:

name value1 value2  
columnname1 2 0.5  
columnname2 1 0.75  
columnname3 3 0.25  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi anonymous user,
The names come from the UNPIVOT. UNPIVOT converts columns to rows. For example, consider the following

Create table test(RowNumber int, columnname1 nvarchar(255),columnname2 nvarchar(255),columnname3 nvarchar(255))
insert into test
values (1, 'Pink','Red','Blue')
,(2, ' ',' ','Brown')
,(3, ' ',' ','Red')
,(4, 'Yellow',' ',' ')
Select RowNumber, ColumnName, ColumnValue
From test
Unpivot
 (
     ColumnValue
     FOR ColumnName in (columnname1, columnname2, columnname3)
 ) as UnPvt;

The output to that will be

RowNumber ColumnName ColumnValue

1 columnname1 Pink
1 columnname2 Red
1 columnname3 Blue
2 columnname1
2 columnname2
2 columnname3 Brown
3 columnname1
3 columnname2
3 columnname3 Red
4 columnname1 Yellow
4 columnname2
4 columnname3

As you can see, for every row in the original table you know have one row for every column in the column list (columnname1, columnname2, columnname3). So I know have a result with 12 rows and three columns (RowNumber, ColumnName (which has the name of the column in the original table and ColumnValue (which has the value of that column in that row).

The names ColumnName and ColumnValue come from the Unpivot. They can be anything you want. So you could, for example, use

Select RowNumber, DataSource, Color
From test
Unpivot
 (
     Color
     FOR DataSource in (columnname1, columnname2, columnname3)
 ) as UnPvt;

Then your result would be

RowNumber DataSource Color
1 columnname1 Pink
1 columnname2 Red
1 columnname3 Blue
2 columnname1
2 columnname2
2 columnname3 Brown
3 columnname1
3 columnname2
3 columnname3 Red
4 columnname1 Yellow
4 columnname2
4 columnname3

Tom

Answer 5

Hi anonymous user,

Please refer below updated one :

drop table if exists test  
  
Create table test  
(columnname1 nvarchar(255),  
columnname2 nvarchar(255),  
columnname3 nvarchar(255),  
columnname4 nvarchar(255))  
  
insert into test  
values ('Pink','Red','Blue','White')  
,(' ',' ','Brown',' ')  
,(' ',' ','Red','Black')  
,('Yellow',' ',' ',' ')  
  
select name,[Y] as value1,cast((sum-[Y]) as float)/sum as value2  
  from  
  (  
    select Name,iif(Value='','N','Y') value,(select count(*) from test) sum  
    from test  
    unpivot  
    (  
      Value  
      for Name in (columnname1, columnname2,  
                  columnname3,columnname4) --add new column name here  
    ) unp  
  ) src  
  pivot  
  (  
    count(value)  
    for value in ([Y], [N])  
  ) piv  

Output:

name	value1	value2  
columnname1	2	0.5  
columnname2	1	0.75  
columnname3	3	0.25  
columnname4	2	0.5  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.