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               |
+-----+--------------------------------------------+

The table structure will make queries complicated. Do you need to keep the structure? — user2652379
– user2652379, Commented Apr 26, 2018 at 8:27
Seconding the above comment, why is the source table structured like this? — iamdave
– iamdave, Commented Apr 26, 2018 at 8:37
If you had a row with the following data MALE, OTHER, FEMALE, MALE, NULL would you expect to see "1 Male, 1 Other, 1 Female and 1 Male" or "2 Male, 1 Other and 1 Female"? Either could be a valid response to the data shown. — DancingFool
– DancingFool, Commented Apr 26, 2018 at 8:39
Also, how important is the order of the output? Would you be just as happy if the first row said "1 Female and 2 Male"? How about "2 Male, 1 Female" (no "and") — DancingFool
– DancingFool, Commented Apr 26, 2018 at 8:43
@user2652379.. It's a legacy system and hence the structure. We are in process of normalising the tables but till then we have to fulfill the reporting requirements. — Shant
– Shant, Commented Apr 26, 2018 at 9:15

Gordon Linoff · Accepted Answer · 2018-04-26 12:38:33Z

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.

edited Apr 26, 2018 at 12:38

answered Apr 26, 2018 at 11:23

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

iamdave Over a year ago

For ID = 2 the order of the Remarks is incorrect. I agree that this is probably not actually important, but does mean that this doesn't fully answer the question.

iamdave · Accepted Answer · 2018-04-26 10:29:57Z

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 |
+----+------------------------------+

Collectives™ on Stack Overflow

Query to generate string based on values in multiple columns

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related