Group Rows into One Row With Multiple Columns

[tkepongo]

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

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.

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

 

[glenntb]

First, a disclaimer that I'm a little new to SQL and still learning... but I think this might work for you:

SELECT Group_Name, ScheduledYear,             
		CASE WHEN SUM(CASE WHEN Q1 = 'Null' THEN 0 ELSE 1 END) > 0 THEN 'Audit Info' ELSE 'Null' END AS Q1,
		CASE WHEN SUM(CASE WHEN Q2 = 'Null' THEN 0 ELSE 1 END) > 0 THEN 'Audit Info' ELSE 'Null' END AS Q2,
		CASE WHEN SUM(CASE WHEN Q3 = 'Null' THEN 0 ELSE 1 END) > 0 THEN 'Audit Info' ELSE 'Null' END AS Q3,
		CASE WHEN SUM(CASE WHEN Q4 = 'Null' THEN 0 ELSE 1 END) > 0 THEN 'Audit Info' ELSE 'Null' END AS Q4
FROM @T1 t1             
GROUP BY Group_Name, ScheduledYear

-Glenn

 

[tkepongo]

Glenn, it works! Thanks a lot!

Here's another solution someone else provided me

SELECT  MAX(Q1) AS Q1,
        MAX(Q2) AS Q2,
        MAX(Q3) AS Q3,
        MAX(Q4) AS Q4,
        Group_Name,
        ScheduledYear
FROM    v_GroupSchedule
GROUP BY Group_Name,
        ScheduledYear