Query to generate string based on values in multiple columns

Question

I have a table in database that has structure and data as follows:

+-----+--------+--------+--------+--------+--------+
|  ID |  Col1  |  Col2  |  Col3  |  Col4  |  Col5  |
+-----+--------+--------+--------+--------+--------+
|  1  |  MALE  |  MALE  | FEMALE |  NULL  |  NULL  |
|  2  | FEMALE |  MALE  |  NULL  |  NULL  |  NULL  |
|  3  | FEMALE |  NULL  |  NULL  |  NULL  |  NULL  |
|  4  |  MALE  | OTHER  | FEMALE | FEMALE |  NULL  |
|  5  |  MALE  | OTHER  | FEMALE |  MALE  | FEMALE |
+-----+--------+--------+--------+--------+--------+

The order of data has to be in order of first appearance in the columns, from Col1 to Col5, to get the following output:

+-----+--------------------------------------------+
|  ID | Remarks                                    |
+-----+--------------------------------------------+
|  1  | 2 Male and 1 Female                        |
|  2  | 1 Female and 1 Male                        |
|  3  | 1 Female                                   |
|  4  | 1 Male, 1 Other and 2 Female               |
|  5  | 2 Male, 1 Other and 2 Female               |
+-----+--------------------------------------------+

Answer 1

I would do this use apply.

select t.*,
       (case when num_male = 0 and num_female = 0 and num_other = 0
             then ''
             when num_male = 0 and num_female = 0
             then replace('num_other OTHER', 'num_other', num_other)
             when num_male = 0 and num_other = 0
             then replace('num_female FEMALE', 'num_female', num_female)
             when num_male = 0 and num_female = 0
             then replace('num_male MALE', 'num_male', num_male)
             when num_male = 0 
             then replace(replace(replace('num_other OTHER AND num_female FEMALE'), 'num_male', num_male), 'num_other', num_other), 'num_female', num_female)
             when num_other = 0 
             then replace(replace(replace('num_male MALE AND num_female FEMALE), 'num_male', num_male), 'num_other', num_other), 'num_female', num_female)
             when num_female = 0 
             then replace(replace(replace('num_male MALE AND num_other OTHER), 'num_male', num_male), 'num_other', num_other), 'num_female', num_female)
             else replace(replace(replace('num_male MALE, num_other OTHER AND num_female FEMALE), 'num_male', num_male), 'num_other', num_other), 'num_female', num_female)
        end) as remarks
from t cross apply
     (select sum(case when col = 'FEMALE' then 1 else 0 end) as num_females,
             sum(case when col = 'MALE' then 1 else 0 end) as num_males,
             sum(case when col = 'OTHER' then 1 else 0 end) as num_other
      from (values (col1), (col2), (col3), (col4), (col5)) v(col)
     ) v;

I don't see an advantage to cleverness in calculating the remarks structure.

Answer 2

This is a very unusual requirement and I would imagine that you don't really need to have the values in the order you are asking for, but to answer the question as asked:

-- Build data
declare @t table(ID int,Col1 varchar(10),Col2 varchar(10),Col3 varchar(10),Col4 varchar(10),Col5 varchar(10));
insert into @t values
 (1,'MALE','MALE','FEMALE',null,null)
,(2,'FEMALE','MALE',null,null,null)
,(3,'FEMALE',null,null,null,null)
,(4,'MALE','OTHER','FEMALE','FEMALE',null)
,(5,'MALE','OTHER','FEMALE','MALE','FEMALE')
;

-- Query
with d as
(   -- Manually unpivot the 5 Cols and add a row number
    select 1 as r
            ,ID
            ,Col1 as Col
    from @t
    union all
    select 2
            ,ID
            ,Col2 as Col
    from @t
    union all
    select 3
            ,ID
            ,Col3 as Col
    from @t
    union all
    select 4
            ,ID
            ,Col4 as Col
    from @t
    union all
    select 5
            ,ID
            ,Col5 as Col
    from @t
)
,c as
(   -- Replace Col values for presentation and add in aggregated row numbers, ranks and counts for each value
    select ID
            ,case Col when 'MALE' then 'Male'
                        when 'FEMALE' then 'Female'
                        when 'OTHER' then 'Other'
                        end as Col
            ,row_number() over (partition by ID order by r) as rn
            ,dense_rank() over (partition by ID order by r) as drk
            ,rank() over (partition by ID, Col order by r) as rk
            ,count(Col) over (partition by ID, Col) as c
    from d
    where Col is not null
)
,o as
(   -- Filter rows down to just the first instance of that Col value to get the presentation order
    select ID
            ,row_number() over (partition by ID order by rn) as o
            ,cast(c as varchar(10)) + ' ' + Col as c
    from c
    where rk = 1
)
    -- Collate the data per presentation rules
select o.ID
        ,o.c
         + isnull(case when o3.ID is null
                    then ' and ' + o2.c
                    else ', ' + o2.c + ' and ' + o3.c
                    end
                    ,'') as Remarks
from o
    left join o as o2
        on o.ID = o2.ID
            and o2.o = 2
    left join o as o3
        on o.ID = o3.ID
            and o3.o = 3
where o.o = 1
order by o.ID;

Output:

+----+------------------------------+
| ID |           Remarks            |
+----+------------------------------+
|  1 | 2 Male and 1 Female          |
|  2 | 1 Female and 1 Male          |
|  3 | 1 Female                     |
|  4 | 1 Male, 1 Other and 2 Female |
|  5 | 2 Male, 1 Other and 2 Female |
+----+------------------------------+