SQL - how to - minimum number of steps

Hi. I have an 'Attendance' table like this:

PIN Year Category Days
1 2006 Authorized 1
1 2006 Available 2
1 2006 Personal 3
2 2006 Authorized 4
2 2006 Available 5
2 2006 Personal 6
3 2006 Authorized 7
3 2006 Available 8
3 2006 Personal 9
4 2006 Authorized 10
4 2006 Available 11
4 2006 Personal 12
1 2007 Authorized 13
1 2007 Available 14
1 2007 Personal 15
2 2007 Authorized 16
2 2007 Available 17
2 2007 Personal 18
3 2007 Authorized 19
3 2007 Available 20
3 2007 Personal 21
4 2007 Authorized 22
4 2007 Available 23
4 2007 Personal 24


I need to sum the days by PIN, Year and Category (that's easy...) AND
obtain a layout like this:


PIN Auth 2006 Avail 2006 Pers 2006 Auth
2007 Avail 2007 Pers 2007
1 1 2
3 13 14 15
2 4 5
6 16 17 18
3 7 8
9 19 20 21
4 10 11
12 22 23 24


How can I do this by queries without writing too many intermediate
steps ?


What I have done is this (5 queries, 2, 3, and 4 building on top of
1,
and 5 building on 2, 3, 4).


1 = Table1_Crosstab:


TRANSFORM Sum(Table1.Days) AS SumOfDays
SELECT Table1.PIN, Table1.Year
FROM Table1
GROUP BY Table1.PIN, Table1.Year
PIVOT Table1.Category;


Then, based on that,


2 = Authorized:


TRANSFORM First([1 = Table1_Crosstab].Authorized) AS
FirstOfAuthorized
SELECT [1 = Table1_Crosstab].PIN
FROM [1 = Table1_Crosstab]
GROUP BY [1 = Table1_Crosstab].PIN
PIVOT [1 = Table1_Crosstab].Year;


3 = Available:


TRANSFORM First([1 = Table1_Crosstab].Available) AS FirstOfAvailable
SELECT [1 = Table1_Crosstab].PIN
FROM [1 = Table1_Crosstab]
GROUP BY [1 = Table1_Crosstab].PIN
PIVOT [1 = Table1_Crosstab].Year;


and


4 = Personal:


TRANSFORM First([1 = Table1_Crosstab].Personal) AS FirstOfPersonal
SELECT [1 = Table1_Crosstab].PIN
FROM [1 = Table1_Crosstab]
GROUP BY [1 = Table1_Crosstab].PIN
PIVOT [1 = Table1_Crosstab].Year;


and finally


5 = All


SELECT [2 = Authorized].PIN, [2 = Authorized].[2006] AS [Auth 2006],
[3 = Available].[2006] AS [Avail 2006], [4 = Personal].[2006] AS
[Pers
2006], [2 = Authorized].[2007] AS [Auth 2007], [3 = Available].[2007]
AS [Avail 2007], [4 = Personal].[2007] AS [Pers 2007]
FROM ([2 = Authorized] INNER JOIN [3 = Available] ON [2 =
Authorized].PIN = [3 = Available].PIN) INNER JOIN [4 = Personal] ON
[3
= Available].PIN = [4 = Personal].PIN;


It works, but... I am sure that this is an awkward way of doing it.
Is
there any other, more elegant, way, please ? Besides, what if I had
not 3, but 15 categories, for example ????


Thanks a lot for your time reading this, Alex

Re: SQL - how to - minimum number of steps

select PIN,
max(case when Category='Authorized' and Year=2006 then Days
end) as [Auth 2006],
max(case when Category='Available' and Year=2006 then Days end)
as [Avail 2006],
max(case when Category='Personal' and Year=2006 then Days end)
as [Pers 2006],
max(case when Category='Authorized' and Year=2007 then Days
end) as [Auth 2007],
max(case when Category='Available' and Year=2007 then Days end)
as [Avail 2007],
max(case when Category='Personal' and Year=2007 then Days end)
as [Pers 2006]
from Attendance
group by PIN
order by PIN

Re: SQL - how to - minimum number of steps

I did this with two queries, I firest created combine_header:

SELECT
PIN,
[year] & " " & Left([category],5) AS Combined,
Days
FROM
Attendance

Then I created a crosstab query:
TRANSFORM First(Days) AS FirstOfDays
SELECT PIN
FROM combine_header
GROUP BY PIN
PIVOT Combined;

Cheers,
Jason Lepack

On Jun 20, 8:36 am, Radu wrote:
> Hi. I have an 'Attendance' table like this:
>
> PIN Year Category Days
> 1 2006 Authorized 1
> 1 2006 Available 2
> 1 2006 Personal 3
> 2 2006 Authorized 4
> 2 2006 Available 5
> 2 2006 Personal 6
> 3 2006 Authorized 7
> 3 2006 Available 8
> 3 2006 Personal 9
> 4 2006 Authorized 10
> 4 2006 Available 11
> 4 2006 Personal 12
> 1 2007 Authorized 13
> 1 2007 Available 14
> 1 2007 Personal 15
> 2 2007 Authorized 16
> 2 2007 Available 17
> 2 2007 Personal 18
> 3 2007 Authorized 19
> 3 2007 Available 20
> 3 2007 Personal 21
> 4 2007 Authorized 22
> 4 2007 Available 23
> 4 2007 Personal 24
>
> I need to sum the days by PIN, Year and Category (that's easy...) AND
> obtain a layout like this:
>
> PIN Auth 2006 Avail 2006 Pers 2006 Auth
> 2007 Avail 2007 Pers 2007
> 1 1 2
> 3 13 14 15
> 2 4 5
> 6 16 17 18
> 3 7 8
> 9 19 20 21
> 4 10 11
> 12 22 23 24
>
> How can I do this by queries without writing too many intermediate
> steps ?
>
> What I have done is this (5 queries, 2, 3, and 4 building on top of
> 1,
> and 5 building on 2, 3, 4).
>
> 1 = Table1_Crosstab:
>
> TRANSFORM Sum(Table1.Days) AS SumOfDays
> SELECT Table1.PIN, Table1.Year
> FROM Table1
> GROUP BY Table1.PIN, Table1.Year
> PIVOT Table1.Category;
>
> Then, based on that,
>
> 2 = Authorized:
>
> TRANSFORM First([1 = Table1_Crosstab].Authorized) AS
> FirstOfAuthorized
> SELECT [1 = Table1_Crosstab].PIN
> FROM [1 = Table1_Crosstab]
> GROUP BY [1 = Table1_Crosstab].PIN
> PIVOT [1 = Table1_Crosstab].Year;
>
> 3 = Available:
>
> TRANSFORM First([1 = Table1_Crosstab].Available) AS FirstOfAvailable
> SELECT [1 = Table1_Crosstab].PIN
> FROM [1 = Table1_Crosstab]
> GROUP BY [1 = Table1_Crosstab].PIN
> PIVOT [1 = Table1_Crosstab].Year;
>
> and
>
> 4 = Personal:
>
> TRANSFORM First([1 = Table1_Crosstab].Personal) AS FirstOfPersonal
> SELECT [1 = Table1_Crosstab].PIN
> FROM [1 = Table1_Crosstab]
> GROUP BY [1 = Table1_Crosstab].PIN
> PIVOT [1 = Table1_Crosstab].Year;
>
> and finally
>
> 5 = All
>
> SELECT [2 = Authorized].PIN, [2 = Authorized].[2006] AS [Auth 2006],
> [3 = Available].[2006] AS [Avail 2006], [4 = Personal].[2006] AS
> [Pers
> 2006], [2 = Authorized].[2007] AS [Auth 2007], [3 = Available].[2007]
> AS [Avail 2007], [4 = Personal].[2007] AS [Pers 2007]
> FROM ([2 = Authorized] INNER JOIN [3 = Available] ON [2 =
> Authorized].PIN = [3 = Available].PIN) INNER JOIN [4 = Personal] ON
> [3
> = Available].PIN = [4 = Personal].PIN;
>
> It works, but... I am sure that this is an awkward way of doing it.
> Is
> there any other, more elegant, way, please ? Besides, what if I had
> not 3, but 15 categories, for example ????
>
> Thanks a lot for your time reading this, Alex

Re: SQL - how to - minimum number of steps

On Jun 20, 8:53 am, Jason Lepack wrote:
> I did this with two queries, I firest created combine_header:
>
> SELECT
> PIN,
> [year] & " " & Left([category],5) AS Combined,
> Days
> FROM
> Attendance
>
> Then I created a crosstab query:
> TRANSFORM First(Days) AS FirstOfDays
> SELECT PIN
> FROM combine_header
> GROUP BY PIN
> PIVOT Combined;
>
> Cheers,
> Jason Lepack
>
> On Jun 20, 8:36 am, Radu wrote:
>
>
>
> > Hi. I have an 'Attendance' table like this:
>
> > PIN Year Category Days
> > 1 2006 Authorized 1
> > 1 2006 Available 2
> > 1 2006 Personal 3
> > 2 2006 Authorized 4
> > 2 2006 Available 5
> > 2 2006 Personal 6
> > 3 2006 Authorized 7
> > 3 2006 Available 8
> > 3 2006 Personal 9
> > 4 2006 Authorized 10
> > 4 2006 Available 11
> > 4 2006 Personal 12
> > 1 2007 Authorized 13
> > 1 2007 Available 14
> > 1 2007 Personal 15
> > 2 2007 Authorized 16
> > 2 2007 Available 17
> > 2 2007 Personal 18
> > 3 2007 Authorized 19
> > 3 2007 Available 20
> > 3 2007 Personal 21
> > 4 2007 Authorized 22
> > 4 2007 Available 23
> > 4 2007 Personal 24
>
> > I need to sum the days by PIN, Year and Category (that's easy...) AND
> > obtain a layout like this:
>
> > PIN Auth 2006 Avail 2006 Pers 2006 Auth
> > 2007 Avail 2007 Pers 2007
> > 1 1 2
> > 3 13 14 15
> > 2 4 5
> > 6 16 17 18
> > 3 7 8
> > 9 19 20 21
> > 4 10 11
> > 12 22 23 24
>
> > How can I do this by queries without writing too many intermediate
> > steps ?
>
> > What I have done is this (5 queries, 2, 3, and 4 building on top of
> > 1,
> > and 5 building on 2, 3, 4).
>
> > 1 = Table1_Crosstab:
>
> > TRANSFORM Sum(Table1.Days) AS SumOfDays
> > SELECT Table1.PIN, Table1.Year
> > FROM Table1
> > GROUP BY Table1.PIN, Table1.Year
> > PIVOT Table1.Category;
>
> > Then, based on that,
>
> > 2 = Authorized:
>
> > TRANSFORM First([1 = Table1_Crosstab].Authorized) AS
> > FirstOfAuthorized
> > SELECT [1 = Table1_Crosstab].PIN
> > FROM [1 = Table1_Crosstab]
> > GROUP BY [1 = Table1_Crosstab].PIN
> > PIVOT [1 = Table1_Crosstab].Year;
>
> > 3 = Available:
>
> > TRANSFORM First([1 = Table1_Crosstab].Available) AS FirstOfAvailable
> > SELECT [1 = Table1_Crosstab].PIN
> > FROM [1 = Table1_Crosstab]
> > GROUP BY [1 = Table1_Crosstab].PIN
> > PIVOT [1 = Table1_Crosstab].Year;
>
> > and
>
> > 4 = Personal:
>
> > TRANSFORM First([1 = Table1_Crosstab].Personal) AS FirstOfPersonal
> > SELECT [1 = Table1_Crosstab].PIN
> > FROM [1 = Table1_Crosstab]
> > GROUP BY [1 = Table1_Crosstab].PIN
> > PIVOT [1 = Table1_Crosstab].Year;
>
> > and finally
>
> > 5 = All
>
> > SELECT [2 = Authorized].PIN, [2 = Authorized].[2006] AS [Auth 2006],
> > [3 = Available].[2006] AS [Avail 2006], [4 = Personal].[2006] AS
> > [Pers
> > 2006], [2 = Authorized].[2007] AS [Auth 2007], [3 = Available].[2007]
> > AS [Avail 2007], [4 = Personal].[2007] AS [Pers 2007]
> > FROM ([2 = Authorized] INNER JOIN [3 = Available] ON [2 =
> > Authorized].PIN = [3 = Available].PIN) INNER JOIN [4 = Personal] ON
> > [3
> > = Available].PIN = [4 = Personal].PIN;
>
> > It works, but... I am sure that this is an awkward way of doing it.
> > Is
> > there any other, more elegant, way, please ? Besides, what if I had
> > not 3, but 15 categories, for example ????
>
> > Thanks a lot for your time reading this, Alex- Hide quoted text -
>
> - Show quoted text -



Thank you VERY MUCH - both solutions work very well - Mark's is only
one step, and Jason's has the advantage of also working in Access...

Thanks a lot, and have a great day !

Alex

Re: SQL - how to - minimum number of steps

I would like to point out that each time you add a Category or a year
you need to modify Mark's query (while it works perfectly for the
given data set). Mine will work with whatever data you load into that
table.

On Jun 20, 11:06 am, Radu wrote:
> On Jun 20, 8:53 am, Jason Lepack wrote:
>
>
>
>
>
> > I did this with two queries, I firest created combine_header:
>
> > SELECT
> > PIN,
> > [year] & " " & Left([category],5) AS Combined,
> > Days
> > FROM
> > Attendance
>
> > Then I created a crosstab query:
> > TRANSFORM First(Days) AS FirstOfDays
> > SELECT PIN
> > FROM combine_header
> > GROUP BY PIN
> > PIVOT Combined;
>
> > Cheers,
> > Jason Lepack
>
> > On Jun 20, 8:36 am, Radu wrote:
>
> > > Hi. I have an 'Attendance' table like this:
>
> > > PIN Year Category Days
> > > 1 2006 Authorized 1
> > > 1 2006 Available 2
> > > 1 2006 Personal 3
> > > 2 2006 Authorized 4
> > > 2 2006 Available 5
> > > 2 2006 Personal 6
> > > 3 2006 Authorized 7
> > > 3 2006 Available 8
> > > 3 2006 Personal 9
> > > 4 2006 Authorized 10
> > > 4 2006 Available 11
> > > 4 2006 Personal 12
> > > 1 2007 Authorized 13
> > > 1 2007 Available 14
> > > 1 2007 Personal 15
> > > 2 2007 Authorized 16
> > > 2 2007 Available 17
> > > 2 2007 Personal 18
> > > 3 2007 Authorized 19
> > > 3 2007 Available 20
> > > 3 2007 Personal 21
> > > 4 2007 Authorized 22
> > > 4 2007 Available 23
> > > 4 2007 Personal 24
>
> > > I need to sum the days by PIN, Year and Category (that's easy...) AND
> > > obtain a layout like this:
>
> > > PIN Auth 2006 Avail 2006 Pers 2006 Auth
> > > 2007 Avail 2007 Pers 2007
> > > 1 1 2
> > > 3 13 14 15
> > > 2 4 5
> > > 6 16 17 18
> > > 3 7 8
> > > 9 19 20 21
> > > 4 10 11
> > > 12 22 23 24
>
> > > How can I do this by queries without writing too many intermediate
> > > steps ?
>
> > > What I have done is this (5 queries, 2, 3, and 4 building on top of
> > > 1,
> > > and 5 building on 2, 3, 4).
>
> > > 1 = Table1_Crosstab:
>
> > > TRANSFORM Sum(Table1.Days) AS SumOfDays
> > > SELECT Table1.PIN, Table1.Year
> > > FROM Table1
> > > GROUP BY Table1.PIN, Table1.Year
> > > PIVOT Table1.Category;
>
> > > Then, based on that,
>
> > > 2 = Authorized:
>
> > > TRANSFORM First([1 = Table1_Crosstab].Authorized) AS
> > > FirstOfAuthorized
> > > SELECT [1 = Table1_Crosstab].PIN
> > > FROM [1 = Table1_Crosstab]
> > > GROUP BY [1 = Table1_Crosstab].PIN
> > > PIVOT [1 = Table1_Crosstab].Year;
>
> > > 3 = Available:
>
> > > TRANSFORM First([1 = Table1_Crosstab].Available) AS FirstOfAvailable
> > > SELECT [1 = Table1_Crosstab].PIN
> > > FROM [1 = Table1_Crosstab]
> > > GROUP BY [1 = Table1_Crosstab].PIN
> > > PIVOT [1 = Table1_Crosstab].Year;
>
> > > and
>
> > > 4 = Personal:
>
> > > TRANSFORM First([1 = Table1_Crosstab].Personal) AS FirstOfPersonal
> > > SELECT [1 = Table1_Crosstab].PIN
> > > FROM [1 = Table1_Crosstab]
> > > GROUP BY [1 = Table1_Crosstab].PIN
> > > PIVOT [1 = Table1_Crosstab].Year;
>
> > > and finally
>
> > > 5 = All
>
> > > SELECT [2 = Authorized].PIN, [2 = Authorized].[2006] AS [Auth 2006],
> > > [3 = Available].[2006] AS [Avail 2006], [4 = Personal].[2006] AS
> > > [Pers
> > > 2006], [2 = Authorized].[2007] AS [Auth 2007], [3 = Available].[2007]
> > > AS [Avail 2007], [4 = Personal].[2007] AS [Pers 2007]
> > > FROM ([2 = Authorized] INNER JOIN [3 = Available] ON [2 =
> > > Authorized].PIN = [3 = Available].PIN) INNER JOIN [4 = Personal] ON
> > > [3
> > > = Available].PIN = [4 = Personal].PIN;
>
> > > It works, but... I am sure that this is an awkward way of doing it.
> > > Is
> > > there any other, more elegant, way, please ? Besides, what if I had
> > > not 3, but 15 categories, for example ????
>
> > > Thanks a lot for your time reading this, Alex- Hide quoted text -
>
> > - Show quoted text -
>
> Thank you VERY MUCH - both solutions work very well - Mark's is only
> one step, and Jason's has the advantage of also working in Access...
>
> Thanks a lot, and have a great day !
>
> Alex- Hide quoted text -
>
> - Show quoted text -