Combining two rows into one

I am trying to combine two rows of data into one row for comparison
purposes and I can do it using a number of steps but thought that there
had to be a way to do it in one SQL statement. Any help would be
appreciated...
Instead of having two rows with the different period end dates, I would
like the query to return the results as follows:
Name, Ticker, CIK, PeriodEndDate, PeriodEndDateLastYear,
NetIncomeCurrentYear, NetIncomeLastYear, OpCashFlowCurrentYear,
OpCashFlowLastYear... All in one row.
Table:
CREATE TABLE [dbo].[CompanyRatios_3Y] (
[Name] [varchar] (160),
[Ticker] [varchar] (10),
[CIK] [varchar] (10),
[PeriodEndDate] [datetime],
[DurationType] [varchar] (3),
[NetIncome] [decimal](38, 6) NULL ,
[OperatingCashFlow] [decimal](38, 6) NULL ,
[TotalAssets] [decimal](38, 6) NULL ,
[TotalRevenue] [decimal](38, 6) NULL
) ON [PRIMARY]
GO
Sample Data:
INSERT INTO CompanyRatios_3
(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
OperatingCashFlow, TotalAssets, TotalRevenue)
VALUES ('ABC Company', 'ABC', '00112233', CONVERT(DATETIME, '2005-12-31
00:00:00', 102), 'TTM', 12345, 23456, 45678, 56789)
INSERT INTO CompanyRatios_3
(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
OperatingCashFlow, TotalAssets, TotalRevenue)
VALUES ('ABC Company', 'ABC', '00112233', CONVERT(DATETIME, '2004-12-31
00:00:00', 102), 'TTM', 23456, 11111, 11111, 22222)
INSERT INTO CompanyRatios_3
(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
OperatingCashFlow, TotalAssets, TotalRevenue)
VALUES ('XYZ Company', 'XYZ', '00332244', CONVERT(DATETIME, '2005-12-31
00:00:00', 102), 'TTM', 22222, 33333, 44444, 55555)
INSERT INTO CompanyRatios_3
(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
OperatingCashFlow, TotalAssets, TotalRevenue)
VALUES ('XYZ Company', 'XYZ', '00332244', CONVERT(DATETIME, '2004-12-31
00:00:00', 102), 'TTM', 33333, 44444, 55555, 66666)
*** Sent via Developersdex http://www.examnotes.net ***try this.
select
a.Name, a.Ticker, a.CIK, a.PeriodEndDate, b.PeriodEndDate as
PeriodEndDateLastYear,
a.Netincome as NetIncomeCurrentYear, b.Netincome as NetIncomeLastYear,
a.operatingcashflow as OpCashFlowCurrentYear,
b.operatingcashflow as OpCashFlowLastYear
from CompanyRatios_3 a
,CompanyRatios_3 b
where a.name = b.name
and a.ticker = b.ticker
and a.cik = b.cik
and year(a.PeriodEndDate) = year(b.periodEndDate) + 1|||Here is one approach, but it makes some assumptions about that data
that I am not comforatble with:
SELECT C.Name,
C.Ticker,
C.CIK,
C.PeriodEndDate,
PeriodEndDateLastYear = P.PeriodEndDate,
NetIncomeCurrentYear = C.NetIncome,
NetIncomeLastYear = P.NetIncome,
OpCashFlowCurrentYear = C.OperatingCashFlow,
OpCashFlowLastYear = P.OperatingCashFlow
FROM CompanyRatios_3 as C -- as in Current
JOIN CompanyRatios_3 as P -- as in Prior
ON C.Ticker = P.Ticker
AND C.CIK = P.CIK
WHERE C.PeriodEndDate = '20051231'
AND P.PeriodEndDate = '20041231'
The problem is that there must be EXACTLY the same Ticker and CIK
values for both years, or you don't get any data for either year.
This can be allowed for, at the price of some complication:
SELECT K.Name,
K.Ticker,
K.CIK,
C.PeriodEndDate,
PeriodEndDateLastYear = P.PeriodEndDate,
NetIncomeCurrentYear = C.NetIncome,
NetIncomeLastYear = P.NetIncome,
OpCashFlowCurrentYear = C.OperatingCashFlow,
OpCashFlowLastYear = P.OperatingCashFlow
FROM (select distinct Name, Ticker, CIK
from CompanyRatios_3) as K -- for Key
LEFT OUTER
JOIN CompanyRatios_3 as C -- as in Current
ON K.Ticker = C.Ticker
AND K.CIK = C.CIK
AND C.PeriodEndDate = '20051231'
JOIN CompanyRatios_3 as P -- as in Prior
ON K.Ticker = P.Ticker
AND K.CIK = P.CIK
AND P.PeriodEndDate = '20041231'
Roy Harvey
Beacon Falls, CT
On Tue, 02 May 2006 13:25:39 -0700, Jason . <jrp210@.yahoo.com> wrote:

>I am trying to combine two rows of data into one row for comparison
>purposes and I can do it using a number of steps but thought that there
>had to be a way to do it in one SQL statement. Any help would be
>appreciated...
>Instead of having two rows with the different period end dates, I would
>like the query to return the results as follows:
>Name, Ticker, CIK, PeriodEndDate, PeriodEndDateLastYear,
>NetIncomeCurrentYear, NetIncomeLastYear, OpCashFlowCurrentYear,
>OpCashFlowLastYear... All in one row.
>Table:
>CREATE TABLE [dbo].[CompanyRatios_3Y] (
> [Name] [varchar] (160),
> [Ticker] [varchar] (10),
> [CIK] [varchar] (10),
> [PeriodEndDate] [datetime],
> [DurationType] [varchar] (3),
> [NetIncome] [decimal](38, 6) NULL ,
> [OperatingCashFlow] [decimal](38, 6) NULL ,
> [TotalAssets] [decimal](38, 6) NULL ,
> [TotalRevenue] [decimal](38, 6) NULL
> ) ON [PRIMARY]
>GO
>Sample Data:
>INSERT INTO CompanyRatios_3
>(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
>OperatingCashFlow, TotalAssets, TotalRevenue)
>VALUES ('ABC Company', 'ABC', '00112233', CONVERT(DATETIME, '2005-12-31
>00:00:00', 102), 'TTM', 12345, 23456, 45678, 56789)
>INSERT INTO CompanyRatios_3
>(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
>OperatingCashFlow, TotalAssets, TotalRevenue)
>VALUES ('ABC Company', 'ABC', '00112233', CONVERT(DATETIME, '2004-12-31
>00:00:00', 102), 'TTM', 23456, 11111, 11111, 22222)
>INSERT INTO CompanyRatios_3
>(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
>OperatingCashFlow, TotalAssets, TotalRevenue)
>VALUES ('XYZ Company', 'XYZ', '00332244', CONVERT(DATETIME, '2005-12-31
>00:00:00', 102), 'TTM', 22222, 33333, 44444, 55555)
>INSERT INTO CompanyRatios_3
>(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
>OperatingCashFlow, TotalAssets, TotalRevenue)
>VALUES ('XYZ Company', 'XYZ', '00332244', CONVERT(DATETIME, '2004-12-31
>00:00:00', 102), 'TTM', 33333, 44444, 55555, 66666)
>
>
>
>*** Sent via Developersdex http://www.examnotes.net ***|||On Tue, 02 May 2006 16:59:16 -0400, Roy Harvey <roy_harvey@.snet.net>
wrote:

>SELECT K.Name,
> K.Ticker,
> K.CIK,
> C.PeriodEndDate,
> PeriodEndDateLastYear = P.PeriodEndDate,
> NetIncomeCurrentYear = C.NetIncome,
> NetIncomeLastYear = P.NetIncome,
> OpCashFlowCurrentYear = C.OperatingCashFlow,
> OpCashFlowLastYear = P.OperatingCashFlow
> FROM (select distinct Name, Ticker, CIK
> from CompanyRatios_3) as K -- for Key
> LEFT OUTER
> JOIN CompanyRatios_3 as C -- as in Current
> ON K.Ticker = C.Ticker
> AND K.CIK = C.CIK
> AND C.PeriodEndDate = '20051231'
> JOIN CompanyRatios_3 as P -- as in Prior
> ON K.Ticker = P.Ticker
> AND K.CIK = P.CIK
> AND P.PeriodEndDate = '20041231'
I missed the second LEFT OUTER:
SELECT K.Name,
K.Ticker,
K.CIK,
C.PeriodEndDate,
PeriodEndDateLastYear = P.PeriodEndDate,
NetIncomeCurrentYear = C.NetIncome,
NetIncomeLastYear = P.NetIncome,
OpCashFlowCurrentYear = C.OperatingCashFlow,
OpCashFlowLastYear = P.OperatingCashFlow
FROM (select distinct Name, Ticker, CIK
from CompanyRatios_3) as K -- for Key
LEFT OUTER
JOIN CompanyRatios_3 as C -- as in Current
ON K.Ticker = C.Ticker
AND K.CIK = C.CIK
AND C.PeriodEndDate = '20051231'
LEFT OUTER
JOIN CompanyRatios_3 as P -- as in Prior
ON K.Ticker = P.Ticker
AND K.CIK = P.CIK
AND P.PeriodEndDate = '20041231'
Roy|||Thanks for both solutions. There will always be a CIK, Ticker, and name
but the dates will not always be 12/31/2005 and 12/31/2004. I was using
those dates as examples.
*** Sent via Developersdex http://www.examnotes.net ***|||On Tue, 02 May 2006 18:29:13 -0700, Jason . <jrp210@.yahoo.com> wrote:

>Thanks for both solutions. There will always be a CIK, Ticker, and name
>but the dates will not always be 12/31/2005 and 12/31/2004. I was using
>those dates as examples.
That should be easily corrected. Just change the date tests:
AND datepart(year, C.PeriodEndDate) = datepart(year,getdate())
AND datepart(year, P.PeriodEndDate) = datepart(year,getdate()) - 1
Roy|||Thanks! There could be more than two dates per CIK so I am guessing I
would have to add the following to get the latest two dates:
WHERE (a.PeriodEndDate =
(SELECT MAX(c.PeriodEndDate)
FROM CompanyRatios_3 c
WHERE a.CIK = c.CIK))
*** Sent via Developersdex http://www.examnotes.net ***|||yeah.. I guess that should do the trick.
You will have to find the max of both this year and the previous year
--
"Jason ." wrote:

> Thanks! There could be more than two dates per CIK so I am guessing I
> would have to add the following to get the latest two dates:
> WHERE (a.PeriodEndDate =
> (SELECT MAX(c.PeriodEndDate)
> FROM CompanyRatios_3 c
> WHERE a.CIK = c.CIK))
>
> *** Sent via Developersdex http://www.developersdex