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 |
|
Dang1337
Starting Member
3 Posts |
 Posted - 2005-08-17 : 11:07:38
|
| Hello, I'm looking for some help on creating a new table. The source table I have looks something like thisAcctNum Social Number Name0000111 555555555 David0000122 555555555 David0000123 555555555 David0009899 333333333 Rose0009896 333333333 Roseand so forth for about 30,000 AccountsI want to create a user file that looks like thisSocial Nubmer Name Acct 1 Acct 2 Acct3 . . . . 555555555 David 111 122 123333333333 Rose 9899 9896Any ideas you guys can throw at me to help? |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-08-17 : 11:20:16
|
| Tell us what this gives you:SELECT MAX(NUM_ACCOUNTS) FROM (SELECT [Social Number], COUNT(*) AS NUM_ACCOUNTS FROM TABLE GROUP BY [Social Number]) AS XXXThis will tell you the max number of Account columns you'll need.But why do this?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
 |
|
|
Dang1337
Starting Member
3 Posts |
Posted - 2005-08-17 : 12:12:59
|
| I must have screwed up because it returned 2269, that is not right.I'm trying to create a user based file that I'm going to import into a new system. |
|
|
|
Dang1337
Starting Member
3 Posts |
Posted - 2005-08-17 : 12:28:41
|
| Well, I got it right this time, and it looks like one user has 189 accounts, but i think I'd like to set a limit to 16 accounts. Anything above that they can call us and we'll manually add. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
From an account table to an user table