Can we use PIVOT here?

[OraMaster]

Hi Experts,

Here we go. I have below DDL,DML and what I expecting.

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 * from langdtl

I need output data as shown below

id   speak_flag   read_flag   ia_flag   lit1_flag   lit2_flag   lit3_flag
-------------------------------------------------------------------------
1    de,en,fi,sv  de,en,fi,sv en,fi     de,fi       de,en,fi    de,sv
2    de,en,fi,sv  en,fi       sv        en,sv       fi,sv       en,fi

lang should have to display in alphabetical order separated by comma.

Thanks!
Bhushan

[gpl]

Could you explain your requirement ?
From the data you posted, I would expected to have seen

1 de,en,fi,sv 
2 en,fi,sv 
 

ps, please use the code tags, it makes it easier to identify what is narrative and what is code and it makes the code clearer

[OraMaster]

Okey here what I get after running one select SQL against my table langdtl.
Please see input in attached file

Now what I need in output is

id   a            b           c         d           e           f
-------------------------------------------------------------------------
1    de,en,fi,sv  de,en,fi,sv en,fi     de,fi       de,en,fi    de,sv
2    de,en,fi,sv  en,fi       sv        en,sv       fi,sv       en,fi

[gpl]

Thanks for reformatting, its a lot clearer now.

First up, create this function, it returns a list of languages in comma-separated format, depending on the parameters.

CREATE FUNCTION dbo.AllLangs (@ID Int, @Which Varchar(255))
RETURNS Varchar(1024)
AS
BEGIN
 
DECLARE @LangList varchar(1024)
 
  SELECT @LangList = COALESCE(@LangList + ',', '') + lang)
  FROM langdtl
  WHERE ID = @ID AND
        Case @Which
          When 'speak_flag' Then speak_flag
          When 'read_flag'  Then read_flag
          When 'ia_flag'    Then ia_flag
          When 'lit1_flag'  Then lit1_flag
          When 'lit2_flag'  Then lit2_flag
          When 'lit3_flag'  Then lit3_flag
        Else 0
        End = 1  
  Order by lang
 
    RETURN @LangList
END
 

Now your query should look like this

select  ID,
        dbo.AllLangs(ID, 'speak_flag') as [speak_flag],
        dbo.AllLangs(ID, 'read_flag')  as [read_flag],
        dbo.AllLangs(ID, 'ia_flag')    as [ia_flag],
        dbo.AllLangs(ID, 'lit1_flag')  as [lit1_flag],
        dbo.AllLangs(ID, 'lit2_flag')  as [lit2_flag],
        dbo.AllLangs(ID, 'lit3_flag')  as [lit3_flag]
From lang
Order by ID
 

thsi should give you the results that you are after

[ck9663]

Here's an alternative code with no function and not an RBAR processing...

The catch:
1. You have to know how many records you have on the language table - You need this on the TOP x portion.
2. You have to know what the maximum number of language are there per ID - You need this on the COALESCE( MIN( CASE WHEN rownum = x ....)) portion, which you need to adjust depending on the value of x.

 
;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 = substring(speak_lang,1, len(speak_lang)-1),
   read_lang = substring(read_lang,1, len(read_lang)-1),
   ia_lang = substring(ia_lang,1, len(ia_lang)-1),
   lit1_lang = substring(lit1_lang,1, len(lit1_lang)-1),
   lit2_lang = substring(lit2_lang,1, len(lit2_lang)-1),
   lit3_lang = substring(lit3_lang,1, len(lit3_lang)-1)
from concatenated   
 
 

The second CTE (the one with TOP 10000000) is necessary to sort the language in descending based on the position of the row. It looks like on your desired output, the last language per ID is the first on the string. The top 10M is a "guess-timate". Just in case your language table is that big. If you don't need the sorting of language remove that. You have to refer the CONCATENATED CTE to LANGUAGE_TABLE CTE instead.

The last CTE is needed to remove the last comma inserted.

If you can't deal with the "The Catch" part, convert the entire thing to a dynamic query.

Happy Coding!!!

~~ CK