May 20, 2009 at 1:33 pm
Is there a way to get these records on a single row. I am not familiar with the CASE function. I am trying to get the 12 periods as columns.
tblPeriods
PeriodId int
FromDate datetime
ThruDate datetime
SELECT CASE WHEN PeriodId = 1 THEN PeriodEnd
END AS Period1,
CASE WHEN PeriodId = 2 THEN PeriodEnd
END AS Period2,
CASE WHEN PeriodId = 3 THEN PeriodEnd
END AS Period3
FROM dbo.tblCmPeriod
Period1Period2Period3
2009-06-14 00:00:00.000NULLNULL
NULL2009-06-21 00:00:00.000NULL
NULLNULL2009-06-28 00:00:00.000
May 20, 2009 at 2:04 pm
R u looking for something like this:
[Code]
SELECT CASE PeriodID WHEN 1 THEN 'Period1'
WHEN 2 THEN 'Period2'
WHEN 3 THEN 'Period3'
ELSE 'Other'
END AS Period
FROM dbo.tblCmPeriod
[/Code]
May 20, 2009 at 2:24 pm
What I am looking for is a single row:
Period1 Period2 Period3
3/1/2009 3/10/2009 3/15/2009
What I am getting is a row per tblCmPeriod record.
Period1 Period2 Period3
3/1/2009 Null Null
Null 3/10/2009 Null
Null Null 3/15/2009
Thanks,
vmon
May 20, 2009 at 2:38 pm
Could u post ur table description(CREATE TABLE statement) and possibly 3 insert statements with your current records in the table? I t would be lot more faster that way so that I/we can test and give u what u want.
May 20, 2009 at 2:43 pm
SELECT MIN(CASE WHEN PeriodId = 1 THEN PeriodEnd END) AS Period1,
MIN(CASE WHEN PeriodId = 2 THEN PeriodEnd END) AS Period2,
MIN(CASE WHEN PeriodId = 3 THEN PeriodEnd END) AS Period3
FROM dbo.tblCmPeriod
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 20, 2009 at 2:49 pm
CREATE TABLE [dbo].[tblCmPeriod](
[RecNumId] [int] IDENTITY(1,1) NOT NULL,
[PeriodId] [varchar](50) NOT NULL,
[PeriodStart] [datetime] NULL,
[PeriodEnd] [datetime] NULL
INSERT INTO dbo.tblCmPeriod (
PeriodId,
PeriodStart,
PeriodEnd
) VALUES (
'1',
'2009-3-5 16:45:38.905',
'2009-3-5 16:45:38.905' ,
INSERT INTO dbo.tblCmPeriod
(
PeriodId
, PeriodStart
, PeriodEnd
)
VALUES (
'2'
, '2009-3-10 16:45:38.905'
, '2009-3-10 16:45:38.905'
)
INSERT INTO dbo.tblCmPeriod
(
PeriodId
, PeriodStart
, PeriodEnd
)
VALUES (
'3'
, '2009-3-20 16:45:38.905'
, '2009-3-20 16:45:38.905'
)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply