October 7, 2008 at 3:41 pm
Hello, I seem to be having brain fade in my old age.
Let me first say thanks to all who can help.
I am trying to return the results of a query with a case statement. I keep getting duplicates instead of one row per person I get three rows for one person and one row for the other people.
I have included table creation scripts and data insert scripts as well as the query I am using.
Names have been changed to protect my job.
Gary
Here is my query:
select fname,lname
,case when e.degreeid =1 then 'Y' else 'N' end 'Bachelors'
,case when e.degreeid =2 then 'Y' else 'N' end 'Masters'
,case when e.degreeid =3 then 'Y' else 'N' end 'PHD'
from students s
join education e on s.studentid = e.studentid
join edu_degrees ed on e.degreeid= ed.degreeid
group by fname,lname ,e.degreeid
RESULTS:
fname lname Bachelors Masters PHD
----------------------------------- --------------------------------------------- --------- ------- ----
Bruce Wayne Y N N
Bruce Wayne N Y N
Bruce Wayne N N Y
Clark Kent Y N N
Tommy Boy Y N N
(5 row(s) affected)
You can see Bruce Wayne is returned three times.
What I am looking for is this:
fname lname Bachelors Masters PHD
----------------------------------- --------------------------------------------- --------- ------- ----
Bruce Wayne Y Y Y
Clark Kent Y N N
Tommy Boy Y N N
Here are the creation and insert scripts:
/****** Object: Table [dbo].[Degrees] Script Date: 10/07/2008 14:09:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Edu_Degrees](
[degreeid] [int] IDENTITY(1,1) NOT NULL,
[degree_name] [varchar](45) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Degrees] PRIMARY KEY CLUSTERED
(
[degreeid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Education] Script Date: 10/07/2008 14:09:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Education](
[educationid] [int] IDENTITY(1,1) NOT NULL,
[studentid] [int] NOT NULL,
[degreeid] [int] NOT NULL,
[date_conferred] [datetime] NOT NULL,
CONSTRAINT [PK_Education] PRIMARY KEY CLUSTERED
(
[educationid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Students] Script Date: 10/07/2008 14:09:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Students](
[studentid] [int] IDENTITY(1,1) NOT NULL,
[FName] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LName] [varchar](45) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED
(
[studentid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Education] WITH CHECK ADD CONSTRAINT [FK_Education_Degrees] FOREIGN KEY([degreeid])
REFERENCES [dbo].[Edu_Degrees] ([degreeid])
GO
ALTER TABLE [dbo].[Education] WITH CHECK ADD CONSTRAINT [FK_Education_Students] FOREIGN KEY([studentid])
REFERENCES [dbo].[Students] ([studentid])
-- Insert statements.
--
--
-- insert into edu_degrees (degree_name)
-- values ('Bachelor')
-- insert into edu_degrees (degree_name)
-- values ('Masters')
-- insert into edu_degrees (degree_name)
-- values ('PHD')
--
-- insert into Students(FName,LName)
-- values ('Tommy','Boy')
-- insert into Students(FName,LName)
-- values ('Joe','Dirt')
-- insert into Students(FName,LName)
-- values ('Bruce','Wayne')
-- insert into Students(FName,LName)
-- values ('Clark','Kent')
-- insert into Students(FName,LName)
-- values ('Hommer','Simpson')
--
--
-- insert into Education (studentid,degreeid,date_conferred)
-- values (1,1,'1995-06-25 00:00:00.000')
-- insert into Education (studentid,degreeid,date_conferred)
-- values (3,1,'1950-05-25 00:00:00.000')
-- insert into Education (studentid,degreeid,date_conferred)
-- values (3,2,'1952-06-19 00:00:00.000')
-- insert into Education (studentid,degreeid,date_conferred)
-- values (3,3,'1956-04-22 00:00:00.000')
-- insert into Education (studentid,degreeid,date_conferred)
-- values (4,1,'1949-07-10 00:00:00.000')
October 7, 2008 at 4:52 pm
I'll admit up front that I didn't read through your post carefully, but I think I know what you need. You want to "collapse" multiple rows into one - that's a GROUP BY. What you want to GROUP BY are all the fields except for the CASE statement you are using (i.e. only one row for each). In this specific case, it's fname, lname. I'll guess you have other fields too though, just copy them into the GROUP BY.
Now for the CASE, what you want to do is something like this: MAX(CASE WHEN e.degree = 1 THEN 'Y' ELSE 'N' END) AS Bachelors
and do the same for the other 2 columns as well.
So when the three rows for Bruce are collapsed, you'll get the max of Y, N, and N which will be Y (because Y is last). If Bruce only had a 2 and a 3 (masters/PHD), then the max of N and N would be... well... N 🙂 For those with only one row (lets say a bachelors), you'll be getting the max of a single value, which will be Y for the first column and N for the next two. I hope that makes sense, I just read it again and I didn't do it very clearly, but I hope it helps!
(you can also do the case like this, or a few other ways:
CASE WHEN MAX(CASE WHEN e.degree = 1 then 1 else 0 END) = 1 THEN 'Y' ELSE 'N' END
this filters out the degree first, but I don't think it gains much)
Chad
October 7, 2008 at 6:03 pm
Thank you for your response.
It is exactly what I was looking for.
I forgot that MAX can actually help with grouping.
Gary
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply