Convert Rows into A Delimited String, Part 2

Read the first part of the article, here. Here's another application of how to concatenate rows into a single string column. This script group concatenate the rows based on a PRIMARY KEY (in this case column ID). Here's the sample data that will be summarized:

id          speak_flag  read_flag   ia_flag     lit1_flag   lang
----------- ----------- ----------- ----------- ----------- ----
1           1           1           1           1           fi
1           1           1           0           0           sv
1           1           1           1           0           en
1           1           1           0           1           de
2           1           1           0           0           fi
2           1           0           1           1           sv
2           1           1           0           1           en

Here's the required output

id          speak_lang    read_lang     ia_lang       lit1_lang
----------- ------------- ------------- ------------- ----------
1           de,en,sv,fi   de,en,sv,fi   en,fi         de,fi
2           en,sv,fi      en,fi         sv            en,sv

Here's the code 1. Populate the sample data

create table langdtl
(
id int,
speak_flag int,
read_flag int,
ia_flag int,
lit1_flag int,
lit2_flag int,
lit3_flag int,
lang nvarchar(2)
)

insert into langdtl values(1,1,1,1,1,1,0,'fi')
insert into langdtl values(1,1,1,0,0,0,1,'sv')
insert into langdtl values(1,1,1,1,0,1,0,'en')
insert into langdtl values(1,1,1,0,1,1,1,'de')

insert into langdtl values(2,1,1,0,0,1,1,'fi')
insert into langdtl values(2,1,0,1,1,1,0,'sv')
insert into langdtl values(2,1,1,0,1,0,1,'en')

select 
   id ,speak_flag ,read_flag ,ia_flag ,lit1_flag ,lang 
from langdtl

2. Summarized and Group the Rows

;with language_table
as 
(select
   id, 
   rownum = row_number() over(partition by id order by id),
   speak_lang = isnull(lang + space(nullif(0, speak_flag)) + ',',''),
   read_lang = isnull(lang + space(nullif(0, read_flag))+ ',',''),
   ia_lang = isnull(lang + space(nullif(0, ia_flag))+ ',',''),
   lit1_lang = isnull(lang + space(nullif(0, lit1_flag))+ ',',''),
   lit2_lang = isnull(lang + space(nullif(0, lit2_flag))+ ',',''),
   lit3_lang = isnull(lang + space(nullif(0, lit3_flag))+ ',',''),
   speak_flag, read_flag, ia_flag, lit2_flag, lit3_flag
 from langdtl  
),
sorted_table 
as
(
select TOP 10000000 * from language_table order by id asc, rownum desc
), 
concatenated
as
(  
select 
   id,   
   speak_lang = 
   COALESCE( MIN( CASE WHEN rownum = 4 THEN speak_lang END) , '' )  +
   COALESCE( MIN( CASE WHEN rownum = 3 THEN speak_lang END) , '' ) + 
   COALESCE( MIN( CASE WHEN rownum = 2 THEN speak_lang END) , '' ) +  
   COALESCE( MIN( CASE WHEN rownum = 1 THEN speak_lang END) , '' ),
 
   read_lang = 
   COALESCE( MIN( CASE WHEN rownum = 4 THEN read_lang END) , '' )  +
   COALESCE( MIN( CASE WHEN rownum = 3 THEN read_lang END) , '' ) + 
   COALESCE( MIN( CASE WHEN rownum = 2 THEN read_lang END) , '' ) +  
   COALESCE( MIN( CASE WHEN rownum = 1 THEN read_lang END) , '' ),
 
   ia_lang = 
   COALESCE( MIN( CASE WHEN rownum = 4 THEN ia_lang END) , '' )  +
   COALESCE( MIN( CASE WHEN rownum = 3 THEN ia_lang END) , '' )  +
   COALESCE( MIN( CASE WHEN rownum = 2 THEN ia_lang END) , '' )  +
   COALESCE( MIN( CASE WHEN rownum = 1 THEN ia_lang END) , '' ), 

   lit1_lang = 
   COALESCE( MIN( CASE WHEN rownum = 4 THEN lit1_lang END) , '' ) +
   COALESCE( MIN( CASE WHEN rownum = 3 THEN lit1_lang END) , '' ) +
   COALESCE( MIN( CASE WHEN rownum = 2 THEN lit1_lang END) , '' ) +
   COALESCE( MIN( CASE WHEN rownum = 1 THEN lit1_lang END) , '' ), 

 
   lit2_lang = 
   COALESCE( MIN( CASE WHEN rownum = 4 THEN lit2_lang END) , '' ) +
   COALESCE( MIN( CASE WHEN rownum = 3 THEN lit2_lang END) , '' ) +
   COALESCE( MIN( CASE WHEN rownum = 2 THEN lit2_lang END) , '' ) +
   COALESCE( MIN( CASE WHEN rownum = 1 THEN lit2_lang END) , '' ), 

   lit3_lang = 
   COALESCE( MIN( CASE WHEN rownum = 4 THEN lit3_lang END) , '' ) +
   COALESCE( MIN( CASE WHEN rownum = 3 THEN lit3_lang END) , '' ) +
   COALESCE( MIN( CASE WHEN rownum = 2 THEN lit3_lang END) , '' ) +
   COALESCE( MIN( CASE WHEN rownum = 1 THEN lit3_lang END) , '' ) 
 
from  sorted_table
group by id
)
select id, 
   speak_lang = rtrim(substring(speak_lang,1, len(speak_lang)-1)),
   read_lang = rtrim(substring(read_lang,1, len(read_lang)-1)),
   ia_lang =  rtrim(substring(ia_lang,1, len(ia_lang)-1)),
   lit1_lang = rtrim(substring(lit1_lang,1, len(lit1_lang)-1))
from concatenated   

~~ CK