Simple SQL statement

Question

I have a table that has 2 columns: Location and job title in MS Access

I want to create a query that can find the sum of job titles in each location.

For example:

Location                   Job Title 
----------------------------------------------
Alabama                   Engineer
Orlando                   Teacher 
Alabama                   Teacher 
Los Angeles               Engineer

The query result to be:

Location     Alabama      Orlando      Los Angeles 
Job Title       
-----------------------------------------------------------------------------
Engineer        1            0              1
Teacher         1            1              0

Thanks and appreciated

Simply use pivot or `CASE` in the query. – AT-2017 Dec 13 '16 at 07:33 — AT-2017, Dec 13 '16 at 07:33

Kinga · Accepted Answer · 2016-12-13T08:39:55.727

2

using this instruction

select location, job_title, count(job_title)
from table
group by location, job_title

the result will be

Alabama Engineer 1
Alabama Teacher 1
Orlando Teacher 1
Los Angeles Engineer 1

After you got this (columns name are location, job_title, sum, table name is table1) you can use

TRANSFORM FIRST(sum)
SELECT job_title
FROM TABLE1
GROUP BY job_title
PIVOT location

and you will get what you wanted

job_title   Alabama Los Angeles Orlando
Engineer    1       1   
Teacher     1                   1

edited Dec 13 '16 at 08:39

answered Dec 13 '16 at 07:42

Kinga

232
1
4
11

Thanks for your prompt answer. however, I need the result in a 2D table as shown above. Am using MS Access. – Milad Dec 13 '16 at 07:59
Look to the http://stackoverflow.com/questions/16546305/pivoting-data-in-ms-access – Kinga Dec 13 '16 at 08:31
Thanks a lot. That was very helpful. – Milad Dec 13 '16 at 10:59

AT-2017 · Answer 2 · 2016-12-13T08:27:12.727

Here is a sample if you have unknown number of columns. It's dynamic sql query:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName) 
                    from yourtable
                    group by ColumnName, id
                    order by id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select value, ColumnName
                from yourtable
            ) x
            pivot 
            (
                max(value)
                for ColumnName in (' + @cols + N')
            ) p '

exec sp_executesql @query;

Note: The above will convert rows to columns if you have no idea how many columns would be there actually.

The following you could try:

FieldA      FieldB  FieldC  FieldD
----------  ------  ------  ------
2013-05-01  A321         1    1120
2013-05-02  A325         1    2261
2013-05-01  A321         2    2120

The Crosstab Query

TRANSFORM First(FieldD) AS FirstOfFieldD
SELECT FieldA, FieldB
FROM 
    (
        SELECT
            FieldA,
            FieldB,
            'Value' & FieldC AS ColumnName,
            FieldD
        FROM Table
    )
GROUP BY FieldA, FieldB
PIVOT ColumnName

Returns

FieldA      FieldB  Value1  Value2
----------  ------  ------  ------
2013-05-01  A321      1120    2120
2013-05-02  A325      2261

Thanks for your prompt answer. however, am not sure how to use this. Am using MS Access. — Milad, Dec 13 '16 at 08:00

Simple SQL statement

2 Answers2