Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011
    Posts
    63

    Unanswered: Group multiple rows with multiple columns into one row

    Is it possible to group multiple rows with multiple columns into a single row? For example with the code below, I would like to create a view to make it look like:

    -- Q1 ------ Q2 ------ Q3 ------ Q4 ----- Group ---Year
    Audit Info|**Null***|**Null***| Audit Info|Accounting| 2011
    **Null***|**Null***|**Null***| Audit Info|Accounting| 2012
    **Null***|**Null***|**Null***| Audit Info|Finance|2011
    Audit Info| Audit Info| Audit Info| Audit Info|Human Resources| 2011


    Code:
    DECLARE @T1 Table (Q1 varchar(500), Q2 varchar (500), Q3 varchar (500), Q4 varchar (500), Group_Name varchar (100), ScheduledYear smallint)            
    insert into @t1            
        select 'NULL', 'Null', 'Audit info', 'Null', 'Information Services', '2011' union            
        select 'NULL', 'Audit info', 'Null', 'Null', 'Information Services', '2011' union            
        select 'NULL', 'Null', 'Null', 'Audit info', 'Information Services', '2011' union            
        select 'NULL', 'Null', 'Null', 'Audit info', 'Finance', '2011' union            
        select 'Audit Info', 'Null', 'Null', 'Null', 'Accounting', '2011' union              
        select 'NULL', 'Null', 'Null', 'Audit info', 'Accounting', '2011' union
    	select 'NULL', 'Null', 'Null', 'Audit info', 'Accounting', '2012' union
    	select 'Audit info', 'Null', 'Null', 'Null', 'Human Resources', '2011' union
    	select 'NULL', 'Audit info', 'Null', 'Null', 'Human Resources', '2011' union
    	select 'NULL', 'Null', 'Audit info', 'Null', 'Human Resources', '2011' union
    	select 'NULL', 'Null', 'Null', 'Audit info', 'Human Resources', '2011' 
    
    select * from @t1
    order by scheduledyear, group_name
    I have tried the code below, but it only works when the quarters are sequential starting from Q1. For example, (Q1,Q2,Q3) will work, but (Q2,Q4), (Q1, Q3,), (Q3,Q4) won't work. It will just display a row with null values.

    Code:
    select Group_Name,ScheduledYear,             
                max(case when sno=1 then Q1 else null end) as Q1,             
                max(case when sno=2 then Q2 else null end) as Q2,             
                max(case when sno=3 then Q3 else null end) as Q3,             
                max(case when sno=4 then Q4 else null end) as Q4             
        from (  select  row_number() over(partition by ScheduledYear,Group_Name order by ScheduledYear,Group_Name) as sno ,            
                        *             
                from @T1) t1             
        group by Group_Name,ScheduledYear
    Last edited by tkepongo; 08-02-11 at 15:58.

  2. #2
    Join Date
    Jul 2011
    Posts
    63
    A solution was provided to me. Here is the code

    Code:
    SELECT  MAX(Q1) AS Q1,
            MAX(Q2) AS Q2,
            MAX(Q3) AS Q3,
            MAX(Q4) AS Q4,
            Group_Name,
            ScheduledYear
    FROM    @T1
    GROUP BY Group_Name,
            ScheduledYear
    ORDER BY Group_Name,
            ScheduledYear ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •