Query to Transpose with values matching column header

Question

This one is a bit tricky. As some of the values in table1 will matches with the column header in table2 and record being group by ID, I tried with below query but failed:

TRANSFORM Persons
SELECT ID, City, Country
FROM Table1
GROUP BY ID, City, Country
PIVOT Group;

@HansUP it's MS Access, i keep putting MySql as a habbit...i'll take it out — Alex, Apr 16 '16 at 21:26
If you add your data as copy&paste-able text, it would be easier to try things. http://www.sensefulsolutions.com/2010/10/format-text-as-table.html (we can use "Parse table" to get the original data). — Andre, May 02 '16 at 18:00
Are ID+City+Country always linked as in the example? i.e. is ID the City ID? — Andre, May 02 '16 at 18:01
@Andre if you're saying if A is always IL then no. Will it be easier if otherwise? Thanks — Alex, May 02 '16 at 19:11
It doesn't really matter - but if City "defines" a row (together with ID), it should be the second column. — Andre, May 02 '16 at 21:31

score 2 · Answer 1 · edited May 23 '17 at 10:29

2

That's just a simple pivot. I don't see anything that gets in the way.

A similar question was "Convert Rows to columns using 'Pivot' in SQL Server".

But I'd rather read up about pivoting with VB and Access. On MSDN you can find the TRANSFORM Statement.

edited May 23 '17 at 10:29

Community

1
1

answered Apr 16 '16 at 21:20

pid

11,472
6
34
63

it seems like the link are for MySql..I've taken away the tab, it's meant for Access, thanks – Alex Apr 16 '16 at 21:50
The second link (TRANSFORM) is for Access. – pid Apr 16 '16 at 22:00
I appreciate it but it's kinda urgent so I can't really 'study' it, I've edited my post on what I attempted, it's still not working – Alex Apr 16 '16 at 22:10
I don't think Pivot is able to achieve what I'm trying to do here. It requires multiple data being tranform instead of 1. Thanks for your hints thou – Alex Apr 16 '16 at 22:40

score 1 · Answer 2 · answered May 02 '16 at 17:37

1

I just tried this SQL.

TRANSFORM Count(Grouping.Country) AS CountOfCountry
SELECT ID, City, Country
FROM Grouping
GROUP BY ID,City,Country
PIVOT Group;

That gives me this.

I think all you can do is count, so the final result will be a little different that what you are showing in your example. I could be wrong, but I think that's as close as you can get.

answered May 02 '16 at 17:37

ASH

20,759
19
87
200

I do need the number display under G1/G2/etc to be correct, but thanks for your attempts! – Alex May 02 '16 at 17:44

score 1 · Accepted Answer · answered May 02 '16 at 21:33

I think you're almost there - you are missing the aggregate function for the TRANSFORM clause (that is SUM in your case), and with Group being a reserved word, it should be in [square brackets].

TRANSFORM Sum(Persons)
SELECT ID, City, Country
FROM Table1
GROUP BY ID, City, Country
PIVOT [Group];

Query to Transpose with values matching column header

3 Answers3