crosstab(?) query

I am trying to do a query on the following data:
Acct Units Person
10 5 Bob
10 6 Ted
10 3 Sam
11 4 Jill
12 4 Mary
I want my recordset to look like:
Acct 1 2 3 . . . .
10 Ted Bob Sam
11 Jill
12 Mary
In other words, my columns would be 1st, 2nd, 3rd, etc... for each
person/account, based on units.
I currently do this by dumping my data in Excel, doing some manipulations,
exporting up into Access and doing a crosstab query. I am hoping I can do
this in one process in SQL. Can someone point me in the right direction?
thanks
DionHave a look here:
http://www.aspfaq.com/show.asp?id=2462
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Dion" <Dion@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F2BE3DD9-41BC-4001-8E81-677731C4569A@.microsoft.com...
>I am trying to do a query on the following data:
> Acct Units Person
> 10 5 Bob
> 10 6 Ted
> 10 3 Sam
> 11 4 Jill
> 12 4 Mary
> I want my recordset to look like:
> Acct 1 2 3 . . . .
> 10 Ted Bob Sam
> 11 Jill
> 12 Mary
> In other words, my columns would be 1st, 2nd, 3rd, etc... for each
> person/account, based on units.
> I currently do this by dumping my data in Excel, doing some manipulations,
> exporting up into Access and doing a crosstab query. I am hoping I can do
> this in one process in SQL. Can someone point me in the right direction?
> thanks
> Dion

Cross-Tab vs SQL query

Hi,
I have a report like this...

Date Sam Tom Harry 2006 2005
02/01/2006 4 1 1 6 2
02/02/2006 3 1 1 4 5
02/03/2006 2 2 0 4 4
---
---

02/12/2006 1 1 1 3 1
-----------
Total -- -- -- -- --

Now I have all the data required for that in one table.

CREATE TABLE t1(
indate DATETIME
,agname VARCHAR(60)
,Polnumber VARCHAR(20)
)


So I have 2 options :
I have to do in in Cross tab or I have to create a query for this.
Is it possible to built a query like this,I mean assigning data as column?

Any help will be greatly appreciated.
Thanks!!Is it possible to built a query like this,I mean assigning data as column?Yep. If there are a fixed number of possible column values you can use CASE. If not then you need to use dynamic SQL (e.g. http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21820764.html#16500817,
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608,
http://www.sqlteam.com/item.asp?ItemID=2955). I've not used any of the above - I just have them bookmarked in case I need them.

BTW - your better option might be the front end doing this if at all possible

HTH|||Thanks a lot pottie for those links.I will give it a try tonight...Thanks once again..:cool: