Transfrom query - How to achieve output (if possible?)

[Andy.S]

I have a requirement to take the following general format of data...

Col A	Col B	Col C	Col D
A1	B1	C1	D1
A1	B1	C1	D2
A1	B1	C1	D3
A2	B2	C2	D1
A2	B2	C2	D4
A2	B2	C2	D5
A3	B3	C3	D2
A3	B3	C3	D3
A3	B3	C3	D6
A3	B3	C3	D7

And in essence, PIVOT the values on Col D to end up with output that looks similar to...

Col A	Col B	Col C
A1	B1	C1	D1	D2	D3
A2	B2	C2	D1	D4	D5
A3	B3	C3	D2	D3	D6	D7

The values in Col D are in reality a 4 digit code, so potentially many hundreds of values altogether.

The best I have got so far is more like...

Col A	Col B	Col C	D1	D2	D3	D4	D5	D6	D7
A1	B1	C1	2	2	2
A2	B2	C2	2			1	1
A3	B3	C3		2	2			1	1

But given the nature of Col D, the list of D values to the right is not good. The query has (arbitrarily) got a a count as the Aggregate on Col A, hence the numbers showing.

So ...

TRANSFORM COUNT(A & B & C)
Select Distinct A, B,C
FROM TABLE A
GROUP BY A, B, C
PIVOT D

Any assistance would be great.

Thanks.

[ranman256]

make a query ,Q1, of the data you want to use
then use the query wizard, to make a Crosstab query on Q1
pick your rows
pick your columns
pick your values

[Andy.S]

Thank you for the prompt reply, but the crosstab still appears to be a count rather than the specific values and it ends up with (in my sample) over 800 columns which the tool fails with.

The requirement here does now have a potential option, as the 'real' query is going into some application code to ultimately produce the desired CSV report output. Speaking to the coder, he can take what I had as the first sample output (with Col D containing all the required values) and simply loop through row by row and on change of D put the output to the right and on change of Col A/B/C add a new row. It is post processing the data but probably less complex as a result.