Pivot Results

[PiB311]

Hello everyone. Hope you can help.

I have a table that looks similar to this:

Group Reason Date
grp1 New 9/1/2008
grp1 Old 9/1/2008

As you can see, for grp1 there are two reasons. I need a query to create
data that shows the following.

Group Reason1 Reason2 Reason3 Reason4 Date
grp1 New Old
9/1/2008

Where there are an infinite possible amount of reasons. Please let me know
if you have suggestions or if this can't be done at all.

Thank you in advance

 

[Michel Walsh]

Rank your reasons, then make the pivot on that rank.

To rank the reason, you can use your preferred formulation, and if there is
no dup, you can use:

SELECT a.[group], a.reason, a.[date] AS theDate, COUNT(*) AS rank
FROM yourTable AS a INNER JOIN yourTable AS b
ON a.[group]=b.[group] AND a.reason >= b.reason
GROUP BY a.[group], theDate, a.reason



Then:

TRANSFORM LAST(theReason)
SELECT [group], theDate
FROM queryRank
GROUP BY [group], theDate
PIVOT "Reason" & rank



Vanderghast, Access MVP