Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lunchblaze
Starting Member
3 Posts |
 Posted - 2004-10-29 : 15:44:38
|
| have table in standard format:name date of visitbill 1/1/01bill 2/1/01bill 3/1/01steve 2/1/01steve 3/1/01what i want is a query to turn each name into one row such as:bill 1/1/01 2/1/01 3/1/01steve 2/1/01 3/1/01etc.sorry if this is a repeat question.apparently this is done in normal sql with a cursor (which i'm not familiar with)searching on cursor turned up some hits, but nothing jumped out as anything i could use.thanks! |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-29 : 16:15:17
|
| search Access help for Cross-Tab (or maybe CrossTab)- Jeff |
 |
|
|
lunchblaze
Starting Member
3 Posts |
Posted - 2004-10-29 : 16:19:06
|
| hey,thanks for the reply.crosstab was the first way i tried to approach it,the problem with that is two fold1. you must have 3 fields to do a crosstab and one of them needs to be numeric to do an aggregate function on (sum, avg, max, etc.)2. there are so many dates that it would bomb even if i could crosstab itam i missing something about crosstabs maybe? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-29 : 16:38:47
|
| What are you trying to do? Do you just want a query to return two columns: a name, and a list of dates concatenated together?If it ends up being 300 dates next to a name, what are you going to do with THAT ? Display it on a page or the screen or something? Doesn't seem very useful!It might be best to tell us what your desired goal of all this is.- Jeff |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-10-29 : 16:38:54
|
quote:
1. you must have 3 fields to do a crosstab and one of them needs to be numeric to do an aggregate function on (sum, avg, max, etc.)
No, min and max can be used for any data type except text, ntext, image or uniqueidentifier. Dates and strings can be pivoted with no problem, I do it all the time.How many date values are you talking about? You can use a modified version of this procedure:http://www.sqlteam.com/item.asp?ItemID=2955To limit the date range that you want to pivot. There's a reply in the comments section that can do this (about 4-5 replies down):http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6216 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-10-29 : 16:47:00
|
| I just noticed that this is in the Access forum, are you trying to do this in Access or SQL Server?Access has a crosstab feature called TRANSFORM, it can cross-tab any type of data. |
|
|
|
lunchblaze
Starting Member
3 Posts |
Posted - 2004-10-29 : 16:49:34
|
| yes i suppose that's true about min/maxing any tpye of field - although my problem still stands that i don't have a 3rd field. guess i could just insert some bit in there to trick it.anyway, there are over 20,000 records, which would be 20,000 columns if i crosstabbed it.the main goal is to calculate the days between the visit dates for each name - eventually winding them up on an excel sheet to send to another user to do some analysis with.thanks. |
|
|
|
|
|
|
|
|
colmnar to umm, "row-ular" data