SQL Query to transpose data from rows into columns

[Haas C]

Hi all,

I have a table with two columns, labeled Year and Loss. In the Year
field, I have the numbers 1 to 10,000, each which can or cannot
repeat. In the Loss column, i have numbers corresponding to the
Years...for example:

Year, Loss
1, 568
1, 621
1, 358
1, 7888
2, 2689
2, 6563
2, 15
3, 983
3, 146
3, 258
3, 852
4, 96
5, 87
5, 32

So, you see, Year 1 can have four losses, Year 2 can have three
losses, etc.

Now, here's where I need help as I'm not sure what to do given that I
am a beginner at SQl and learning: I want to have just one row for
each Year. So, for Year 1, I would like to have four extra columns,
and for Year 2, I would like to have three columns created to hold
loss numbers so they display in a row - please see below for example:

Year, Loss1, Loss2, Loss3, Loss4, Loss5, etc.
1, 568, 621, 358, 7888
2, 2689, 6563, 15
3, 983, 146, 258, 852
4, 96
5, 87, 32

I hope that the examples helped in clarifying what kind of Query I
need. I believe I can have up to 30 losses for each year. Please help
if you can - I truly appreciate it and thank you in advance!

[Steve]

Re: SQL Query to transpose data from rows into columns

Hello Haas,

A crosstab query will do what you want. Go to queries and click New. One of
your choices will be a crosstab query. Choose it and follow the wizard.

Steve


"Haas C" <haas786@yahoo. comwrote in message
news:a35c34ad-a1e8-4665-9a69-73c639e023b8@26 g2000hsk.google groups.com...

Hi all,
>
I have a table with two columns, labeled Year and Loss. In the Year
field, I have the numbers 1 to 10,000, each which can or cannot
repeat. In the Loss column, i have numbers corresponding to the
Years...for example:
>
Year, Loss
1, 568
1, 621
1, 358
1, 7888
2, 2689
2, 6563
2, 15
3, 983
3, 146
3, 258
3, 852
4, 96
5, 87
5, 32
>
So, you see, Year 1 can have four losses, Year 2 can have three
losses, etc.
>
Now, here's where I need help as I'm not sure what to do given that I
am a beginner at SQl and learning: I want to have just one row for
each Year. So, for Year 1, I would like to have four extra columns,
and for Year 2, I would like to have three columns created to hold
loss numbers so they display in a row - please see below for example:
>
Year, Loss1, Loss2, Loss3, Loss4, Loss5, etc.
1, 568, 621, 358, 7888
2, 2689, 6563, 15
3, 983, 146, 258, 852
4, 96
5, 87, 32
>
I hope that the examples helped in clarifying what kind of Query I
need. I believe I can have up to 30 losses for each year. Please help
if you can - I truly appreciate it and thank you in advance!

[Haas C]

Re: SQL Query to transpose data from rows into columns

On Aug 28, 2:57 pm, "Steve" <nonse...@nomse nse.comwrote:

Hello Haas,
>
A crosstab query will do what you want. Go to queries and click New. One of
your choices will be a crosstab query. Choose it and follow the wizard.
>
Steve
>
"Haas C" <haas...@yahoo. comwrote in message
>
news:a35c34ad-a1e8-4665-9a69-73c639e023b8@26 g2000hsk.google groups.com...
>
>
>

Hi all,

>

I have a table with two columns, labeled Year and Loss. In the Year
field, I have the numbers 1 to 10,000, each which can or cannot
repeat. In the Loss column, i have numbers corresponding to the
Years...for example:

>

Year, Loss
1, 568
1, 621
1, 358
1, 7888
2, 2689
2, 6563
2, 15
3, 983
3, 146
3, 258
3, 852
4, 96
5, 87
5, 32

>

So, you see, Year 1 can have four losses, Year 2 can have three
losses, etc.

>

Now, here's where I need help as I'm not sure what to do given that I
am a beginner at SQl and learning: I want to have just one row for
each Year. So, for Year 1, I would like to have four extra columns,
and for Year 2, I would like to have three columns created to hold
loss numbers so they display in a row - please see below for example:

>

Year, Loss1, Loss2, Loss3, Loss4, Loss5, etc.
1, 568, 621, 358, 7888
2, 2689, 6563, 15
3, 983, 146, 258, 852
4, 96
5, 87, 32

>

I hope that the examples helped in clarifying what kind of Query I
need. I believe I can have up to 30 losses for each year. Please help
if you can - I truly appreciate it and thank you in advance!- Hide quoted text -

>
- Show quoted text -

That actually doesn't work as I get a "too many headers" error. Can
someone please actually show me how to do this rather than tell me to
use a wizard? I appreciate the help Steve but am against a time crunch
and can't figure this out.

Thanks!

[Steve]

Re: SQL Query to transpose data from rows into columns

Haas,

I created a table named TblLossYer with fields MyYear, Loss and ColTitle. I
put your data in the MyYear and Loss fields. I manually put Loss 1, Loss
2,etc in ColTitle field. Here is the crosstab SQL:

TRANSFORM First(TblYearLo ss.Loss) AS FirstOfLoss
SELECT TblYearLoss.MyY ear
FROM TblYearLoss
GROUP BY TblYearLoss.MyY ear
PIVOT TblYearLoss.Col Title;

You will need to figure out a function to automatically create the value in
ColTitle and then use a calculated field in a query to automatically set the
value for ColTitle. When you do this, base your crosstab query on that query
and delete the ColTitle field in your table.

Steve
rlaird@penn.com




"Haas C" <haas786@yahoo. comwrote in message
news:9f05e3e7-cb96-44f5-8694-959e60cd38b1@34 g2000hsh.google groups.com...
On Aug 28, 2:57 pm, "Steve" <nonse...@nomse nse.comwrote:

Hello Haas,
>
A crosstab query will do what you want. Go to queries and click New. One
of
your choices will be a crosstab query. Choose it and follow the wizard.
>
Steve
>
"Haas C" <haas...@yahoo. comwrote in message
>
news:a35c34ad-a1e8-4665-9a69-73c639e023b8@26 g2000hsk.google groups.com...
>
>
>

Hi all,

>

I have a table with two columns, labeled Year and Loss. In the Year
field, I have the numbers 1 to 10,000, each which can or cannot
repeat. In the Loss column, i have numbers corresponding to the
Years...for example:

>

Year, Loss
1, 568
1, 621
1, 358
1, 7888
2, 2689
2, 6563
2, 15
3, 983
3, 146
3, 258
3, 852
4, 96
5, 87
5, 32

>

So, you see, Year 1 can have four losses, Year 2 can have three
losses, etc.

>

Now, here's where I need help as I'm not sure what to do given that I
am a beginner at SQl and learning: I want to have just one row for
each Year. So, for Year 1, I would like to have four extra columns,
and for Year 2, I would like to have three columns created to hold
loss numbers so they display in a row - please see below for example:

>

Year, Loss1, Loss2, Loss3, Loss4, Loss5, etc.
1, 568, 621, 358, 7888
2, 2689, 6563, 15
3, 983, 146, 258, 852
4, 96
5, 87, 32

>

I hope that the examples helped in clarifying what kind of Query I
need. I believe I can have up to 30 losses for each year. Please help
if you can - I truly appreciate it and thank you in advance!- Hide
quoted text -

>
- Show quoted text -

That actually doesn't work as I get a "too many headers" error. Can
someone please actually show me how to do this rather than tell me to
use a wizard? I appreciate the help Steve but am against a time crunch
and can't figure this out.

Thanks!

[Arch]

Re: SQL Query to transpose data from rows into columns

On Thu, 28 Aug 2008 13:44:05 -0700 (PDT), Haas C <haas786@yahoo. com>
wrote:

>On Aug 28, 2:57 pm, "Steve" <nonse...@nomse nse.comwrote:

>Hello Haas,
>>
>A crosstab query will do what you want. Go to queries and click New. One of
>your choices will be a crosstab query. Choose it and follow the wizard.
>>
>Steve
>>
>"Haas C" <haas...@yahoo. comwrote in message
>>
>news:a35c34a d-a1e8-4665-9a69-73c639e023b8@26 g2000hsk.google groups.com...
>>
>>
>>

Hi all,

>>

I have a table with two columns, labeled Year and Loss. In the Year
field, I have the numbers 1 to 10,000, each which can or cannot
repeat. In the Loss column, i have numbers corresponding to the
Years...for example:

>>

Year, Loss
1, 568
1, 621
1, 358
1, 7888
2, 2689
2, 6563
2, 15
3, 983
3, 146
3, 258
3, 852
4, 96
5, 87
5, 32

>>

So, you see, Year 1 can have four losses, Year 2 can have three
losses, etc.

>>

Now, here's where I need help as I'm not sure what to do given that I
am a beginner at SQl and learning: I want to have just one row for
each Year. So, for Year 1, I would like to have four extra columns,
and for Year 2, I would like to have three columns created to hold
loss numbers so they display in a row - please see below for example:

>>

Year, Loss1, Loss2, Loss3, Loss4, Loss5, etc.
1, 568, 621, 358, 7888
2, 2689, 6563, 15
3, 983, 146, 258, 852
4, 96
5, 87, 32

>>

I hope that the examples helped in clarifying what kind of Query I
need. I believe I can have up to 30 losses for each year. Please help
if you can - I truly appreciate it and thank you in advance!- Hide quoted text -

>>
>- Show quoted text -

>
>That actually doesn't work as I get a "too many headers" error. Can
>someone please actually show me how to do this rather than tell me to
>use a wizard? I appreciate the help Steve but am against a time crunch
>and can't figure this out.
>
>Thanks!

Like so many tasks in this area, there are lots of approaches. I
suspect someone will provide a solution that is substantially
"slicker" than this one, but in case not - this one works exactly as
you've requested.

It uses a small function and then calls that function from a query.
We're assuming the table is named 'years' and the attributes are
'yearnum' and 'loss'.

' -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
Public Function Cram(Y As Long)
Dim SQL As String
Dim L

SQL = "Select Loss from years where Yearnum=" & Y
' watch out for line wrap here-
L = CurrentProject. Connection.Exec ute(SQL).GetStr ing(adClipStrin g, ,
, ",", "")
Cram = Left(L, Len(L) - 1)

End Function
' -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-


Then use the function in a query:

' -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
SELECT [yearnum] & ", " & Cram([yearnum]) AS Haas
FROM years
GROUP BY [yearnum] & ", " & Cram([yearnum]);
' -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

Sidenote - Year is a reserved word in Access, it shouldn't be used for
an attribute name.

HTH,
Arch