By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,375 Members | 3,030 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,375 IT Pros & Developers. It's quick & easy.

How do i transfer rows into columns?

P: 5
How do i transfer rows into columns using query?

Please help me to solve this situation

I have a table that stores records like

Table x
Expand|Select|Wrap|Line Numbers
  1. ID WeekEnd %
  2. 1 10/12/07 70
  3. 1 10/13/07 80
  4. 1 10/14/07 40
  5. 1 10/15/07 45
  6. 2 10/12/07 20
  7. 2 10/13/07 30
  8. 2 10/15/07 30
I want to display the above Table in the following format using query.
Expand|Select|Wrap|Line Numbers
  1. ID 10/12/07 10/13/07 10/14/07 10/15/07
  2. 1     70       80       40       45
  3. 2     20       30       NA       30
I had tried crosstab query but it display only the sum or avg.. functions value not exactly value in the table.

1) Want to display the WeekEnd as Table head and display values accordingly
2) Fill NA for empty fields

Note: Please provide me better solution other than creating a table.

Thanks,
Karthik
Sep 7 '08 #1
Share this Question
Share on Google+
3 Replies


FishVal
Expert 2.5K+
P: 2,653
Helli, Karthik.

Sum() and Avg() are not the only aggregate functions.
E.g. First() will work well in your case.
BTW on table having only unique combinations of [ID] and [WeekEnd] values Sum() and Avg() functions will return exactly what you need. ;)

Kind regards,
Fish
Sep 7 '08 #2

NeoPa
Expert Mod 15k+
P: 31,494
From the fact that you don't mention how you want multiples to be handled, I assume that you have only one record for each position.

If this is true then a CrossTab query with (almost) any of the aggregate functions will work.

The First of 1 == The Avg of 1 == Max of 1 == etc etc.
Sep 7 '08 #3

P: 5
Helli, Karthik.

Sum() and Avg() are not the only aggregate functions.
E.g. First() will work well in your case.
BTW on table having only unique combinations of [ID] and [WeekEnd] values Sum() and Avg() functions will return exactly what you need. ;)

Kind regards,
Fish
--------------------------------------
Hello,

Thanks for giving me the first step to solve the problem.

And there are few more issues in the database. I will try if not i will get back to you with more questions.

Thanks for your support.
Karthik
Sep 9 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.