August 2, 2011 at 4:15 pm
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
August 2, 2011 at 4:49 pm
Nice job posting the question complete with DDL, DML and expected results 🙂
I reworked your DML a bit to provide an actual NULL instead of the string 'NULL'. See if something along these lines works for you:
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
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 ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 2, 2011 at 5:24 pm
THANK YOU THANK YOU THANK YOU!!!
I've been working on this for hours and you've solved it in a few minutes!! Do you know if it is possible to create a view off of this?
THANKS!!
August 2, 2011 at 9:35 pm
tonyppham (8/2/2011)
THANK YOU THANK YOU THANK YOU!!!
You're welcome, HTH 🙂
I've been working on this for hours and you've solved it in a few minutes!! Do you know if it is possible to create a view off of this?
The answer is a "qualified yes". A VIEW is nothing more than a stored SELECT statement that when run delivers a set of data. The "qualified" part is that a set, by definition, has no order. In your case, since a VIEW delivers a set and a set has no order it means you must drop the ORDER BY from your query before you can use it to create a VIEW.
This was the query I provided:
Note: I replaced the table-variable with a concrete table name.
SELECT MAX(Q1) AS Q1,
MAX(Q2) AS Q2,
MAX(Q3) AS Q3,
MAX(Q4) AS Q4,
Group_Name,
ScheduledYear
FROM dbo.Group_Quarter_Schedule
GROUP BY Group_Name,
ScheduledYear
ORDER BY Group_Name,
ScheduledYear ;
This is how you would create a view using the query:
CREATE VIEW dbo.Group_Schedule
AS
SELECT MAX(Q1) AS Q1,
MAX(Q2) AS Q2,
MAX(Q3) AS Q3,
MAX(Q4) AS Q4,
Group_Name,
ScheduledYear
FROM dbo.Group_Quarter_Schedule
-- optional WHERE clause would go here
GROUP BY Group_Name,
ScheduledYear ;
Notice there is no ORDER BY. SQL Server will not guarantee the order of a returned result set unless an ORDER BY is provided in the outermost query.
The query of the VIEW is where you can provide an ORDER BY as needed to get an "ordered set", like this:
SELECT Q1,
Q2,
Q3,
Q4,
Group_Name,
ScheduledYear
FROM dbo.Group_Schedule
-- optional WHERE clause would go here
ORDER BY Group_Name,
ScheduledYear ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 3, 2011 at 9:59 am
Thank you very much! Everything worked out perfectly!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply