Can we use PIVOT here?

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • OraMaster
    New Member
    • Aug 2009
    • 135

    #1

    Can we use PIVOT here?

    Hi Experts,

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

    Code:
    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))
    Code:
    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')
    Code:
    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
    Code:
    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
    New Member
    • Jul 2007
    • 152

    #2
    Could you explain your requirement ?
    From the data you posted, I would expected to have seen
    Code:
    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

    Comment

    • OraMaster
      New Member
      • Aug 2009
      • 135

      #3
      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

      Code:
      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
      Last edited by OraMaster; Oct 13 '10, 12:26 PM. Reason: attached file

      Comment

      • gpl
        New Member
        • Jul 2007
        • 152

        #4
        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.
        Code:
        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
        Code:
        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

        Comment

        • ck9663
          Recognized Expert Specialist
          • Jun 2007
          • 2878

          #5
          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.

          Code:
          ;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

          Comment

          Working...