[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

Pivot Statement?

How would I get the following data

afsAmount     afsDate       cnt
2295               20110101     1
2295               20110201      1
2295               20110301      1
1000               20111101      1
1000               20111201      1

To look like this?

numCnt       amount          startdate             endDate
3                  2295             20110101            20110301
2                  1000             20111101            20111201
0
lrbrister
Asked:
lrbrister
1 Solution
 
Philippe DamervalSenior Analyst ProgrammerCommented:
Doesn't look like a pivot so much as a group by. Are you grouping by amount or by unit of time?
It looks like it's amount, so your statement would look something like this:

SELECT afsAmount, Sum(Cnt) AS numCnt, Min(afsDate) AS startDate, Max(afsDate) AS EndDate
FROM afsTable
GROUP BY afsAmount

HTH

Philippe
0
 
lrbristerAuthor Commented:
damerval:
Almost but not quite...
It's possible to have 3 payments of 2295  over 3 month, then 1 payment 0f 1000 on the 4th month.
then 8 payments of 2295

Your select...lumps the 2295's all together without regard to date sequence.
0
 
lrbristerAuthor Commented:
damerval:
Meant to include this in the last statement.
You can see that the start date of row 2 fals in between the start and end date of row 1...
can't have that. Screenprint
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Alpesh PatelAssistant ConsultantCommented:
SELECT ID, NAME, BIRTH,  [3] as CR1, [4] as  CR1_NAME, [5] as  CR2, [6] as CR2_NAME  [7] as CT1, [8] as CT1_NAME , [9]  as    CK1, [10] as   CK1_NAME
FROM
(SELECT Table1.Id, Table1.Name, Table1.Birth, Table2.Attr, Table2.IName
FROM Tabl1 left outer join Table2 on Table1.ID = Table2.ID) p
PIVOT
(
COUNT (ID)
FOR ATTR        IN
( [3], [5], [7], [9] )
AND INAME IN
( [4], [6], [8], [10] )
) AS pvt
ORDER BY pvt.D;
0
 
manunadhCommented:
select distinct COUNT(afsAmount)over(partition by afsAmount),afsAmount,
min(afsDate)over(partition by afsAmount)StartDate,
max(afsDate)over(partition by afsAmount)EndDate
from tab1
0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
...huh?
0
 
lrbristerAuthor Commented:
damerval:
You got me on the right track...I wound up modifying your code and using in a fetch to build my text.
Points being awarded shortly
0
 
lrbristerAuthor Commented:
Placed me on the correct track.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now