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 |
|
aron.ridgway
Starting Member
1 Post |
 Posted - 2014-04-07 : 11:11:44
|
| Hi There, i have an SQL union view (see bellow) and i want to create a column with an Id number. The reason being the account ID has a mixture of number ID's and Text Id's as the information is coming from 5 different Sage company accounts.Is there an easy way of doing this? Many thanks AronSELECT PLSupplierAccountID AS AccountID, SupplierAccountNumber AS AccountNumber, SupplierAccountName AS AccountName, 'RWL' AS CompanyFROM Roth.dbo.PLSupplierAccountUNION ALLSELECT PLSupplierAccountID AS AccountID, SupplierAccountNumber AS AccountNumber, SupplierAccountName AS AccountName, 'FAH' AS CompanyFROM FAH.dbo.PLSupplierAccountUNION ALLSELECT PLSupplierAccountID AS AccountID, SupplierAccountNumber AS AccountNumber, SupplierAccountName AS AccountName, 'RFG' AS CompanyFROM RFG.dbo.PLSupplierAccountUNION ALLSELECT PLSupplierAccountID AS AccountID, SupplierAccountNumber AS AccountNumber, SupplierAccountName AS AccountName, 'RFP' AS CompanyFROM RFP.dbo.PLSupplierAccountUNION ALLSELECT PLSupplierAccountID AS AccountID, SupplierAccountNumber AS AccountNumber, SupplierAccountName AS AccountName, 'RFW' AS CompanyFROM ALOPS.dbo.PLSupplierAccount |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-04-07 : 13:16:36
|
| You can use the ROW_NUMBER() function.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-04-07 : 13:18:17
|
| Do you want the numbers to have no gaps? If so, that would be some overhead. You'd have to write to a temp table. Or, make the query below a derived table and sort all that data, which would be vastly slower than just using a temp table. |
|
|
|
sqlsaga
Yak Posting Veteran
93 Posts |
|
|
|
|
|
|
|
Auto number for SQL View