SQL Query to transpose data from rows into columns

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!

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@26g2000hsk.googlegroups.com...

re: SQL Query to transpose data from rows into columns

On Aug 28, 2:57*pm, "Steve" <nonse...@nomsense.comwrote: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!

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(TblYearLoss.Loss) AS FirstOfLoss
SELECT TblYearLoss.MyYear
FROM TblYearLoss
GROUP BY TblYearLoss.MyYear
PIVOT TblYearLoss.ColTitle;

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@34g2000hsh.googlegroups.com...
On Aug 28, 2:57 pm, "Steve" <nonse...@nomsense.comwrote: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!

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:
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.Execute(SQL).GetString(a dClipString, ,
, ",", "")
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