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
Larry Bristersr. DeveloperAsked:
Who is Participating?
 
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
 
Larry Bristersr. DeveloperAuthor 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
 
Larry Bristersr. DeveloperAuthor 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
Larry Bristersr. DeveloperAuthor 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
 
Larry Bristersr. DeveloperAuthor Commented:
Placed me on the correct track.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.