We help IT Professionals succeed at work.

Pivot Statement?

302 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

Senior Analyst Programmer
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Larry Bristersr. Developer

Author

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.
Larry Bristersr. Developer

Author

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
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
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;

Commented:
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
Philippe DamervalSenior Analyst Programmer

Commented:
...huh?
Larry Bristersr. Developer

Author

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
Larry Bristersr. Developer

Author

Commented:
Placed me on the correct track.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.