SQL - Cross-tab query results into separate columns

[storemannequin]

The query produces this:

ID     Year
101    2011
200    2012
184    2011
184    2013
133    2014
136    2011
189    2012
152    2013
129    2011
184    2013
136    2011

but I want to change the queried results to look like this (with a count of Years):

    Year            
ID    2011    2012    2013    2014
101    1            
200             1        
184    1                   1    
133                                   1
136    2            
189          1        
152                 1    
129    1            
184                 1

I have been looking all over the internet for a solution to this but I've only seen them for cases when another column in also involved for summing. How can this be done in SQL? Any help much appreciated, I am a newbie in SQL.

 

[Joe4]

No need for a Cross-Tab query or any fancy. Can be done with an Aggregate Query. Just do the following:

1. Create a new query on this table.
2. Add the ID field to the query
3. Add the 4 following calculated fields to your query

2011: IIf([Year]=2011,1,0)
2012: IIf([Year]=2012,1,0)
2013: IIf([Year]=2013,1,0)
2014: IIf([Year]=2014,1,0)

4. Click on the Totals button to make this an Aggregate Query.
5. Change the Totals row under all your calculated fields from "Group By" to "Sum"

This should give you the results you are after.

 

[storemannequin]

No need for a Cross-Tab query or any fancy. Can be done with an Aggregate Query. Just do the following:

1. Create a new query on this table.
2. Add the ID field to the query
3. Add the 4 following calculated fields to your query

2011: IIf([Year]=2011,1,0)
2012: IIf([Year]=2012,1,0)
2013: IIf([Year]=2013,1,0)
2014: IIf([Year]=2014,1,0)

4. Click on the Totals button to make this an Aggregate Query.
5. Change the Totals row under all your calculated fields from "Group By" to "Sum"

This should give you the results you are after.

Thank you for you help, I should note this is actually in Oracle not Access. Would this work the same way?

 

[Joe4]

Should be able to do it in Oracle, it is a pretty straightforward database action. Just may need to make sure the syntax is correct.
The SQL Code of the Access query would look something like:

SELECT Table2.ID, Sum(IIf([Table2]![Year]=2011,1,0)) AS 2011, Sum(IIf([Table2]![Year]=2012,1,0)) AS 2012, Sum(IIf([Table2]![Year]=2013,1,0)) AS 2013, Sum(IIf([Table2]![Year]=2014,1,0)) AS 2014
FROM Table2
GROUP BY Table2.ID;

where Table2 is the name of your table.