Prefix alias for columns in select query

_{Wednesday, November 11, 2009 8:30 AM}

Hi,

I have a Select query and it returns a joined table. I need to distinguish the columns of each table so I need them to be used with aliases. How could I modify my script to auto-insert a prefix to the columns?
Currently my query looks like this:

SELECT * FROM Table1 as T1
INNER JOIN Table2  as T2 ON Table2_PK=Table2_Table2FK

I'd like to have columns in the result table as T1.Column1, T1.Column2, T2.Column1 etc., obviously without specifying alias for each column (there could be tens of them). How could I achieve it?

Andris

_{Wednesday, November 11, 2009 9:03 AM ✅Answered}

Andris,

You can do it the following way:

1. Position to AdventureWorks2008 or your database  in SSMS Query Editor
2. Enter the following or your query:

select *
from Sales.SalesOrderHeader soh
inner join Sales.SalesOrderDetail sod
on soh.SalesOrderID = sod.SalesOrderID

3. Select Design Query in Query drop-down menu

4. You will see  the * replaced by columns with aliases.

5. Press OK at the bottom

Query is replaced the way you wanted it:

SELECT     soh.SalesOrderID, soh.RevisionNumber, soh.OrderDate, soh.DueDate, soh.ShipDate, soh.Status, soh.OnlineOrderFlag, soh.SalesOrderNumber, 
                      soh.PurchaseOrderNumber, soh.AccountNumber, soh.CustomerID, soh.SalesPersonID, soh.TerritoryID, soh.BillToAddressID, soh.ShipToAddressID, 
                      soh.ShipMethodID, soh.CreditCardID, soh.CreditCardApprovalCode, soh.CurrencyRateID, soh.SubTotal, soh.TaxAmt, soh.Freight, soh.TotalDue, 
                      soh.Comment, soh.rowguid, soh.ModifiedDate, sod.SalesOrderID AS Expr1, sod.SalesOrderDetailID, sod.CarrierTrackingNumber, sod.OrderQty, 
                      sod.ProductID, sod.SpecialOfferID, sod.UnitPrice, sod.UnitPriceDiscount, sod.LineTotal, sod.rowguid AS Expr2, sod.ModifiedDate AS Expr3
FROM         Sales.SalesOrderHeader AS soh INNER JOIN
                      Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

_{Wednesday, November 11, 2009 9:07 AM | 1 vote}

HI
nologin
i dont think that there is any staraight away query solution
how ever u can write dynamic sql code to generate select query wherein ull need to  generate aliases for each column

_______________________________________________________________________________
Please mark posts helpful / answered if they answer ur query -- kunal

_{Wednesday, November 11, 2009 9:17 AM}

 Yes Kunal is right, by using Kalman's query you can get all the column names with out using '*', even though you need to specify alias names manually for each and every column name.Lakshman

_{Wednesday, November 11, 2009 9:29 AM}

Seems like a viable solution. Based on this answer I assume there is no 'one word' solution that I'd just put in the query. In my case, I need each columns to be used with alias so I have to insert the AS statements with an algorithm.Andris

_{Wednesday, November 11, 2009 9:59 AM}

nope :( i dont think so .... only possible solution would be writing dynamic sql ...
why do u want results that way by the way ??
i believe there will be fewer columns common in tables ..
u can use method suggest by sqlusa and replace aliases "Expr1...Exprn" with "T2.Column" .. etc  as far as viewing results ...

_______________________________________________________________________________
Please mark posts helpful / answered if they answer ur query -- kunal

_{Wednesday, November 11, 2009 11:42 AM}

Because in the calling program I need to separate the incoming data based on the name and I have to assign the data to a corresponding object ( I can't think of any other possible separation other than this).

Andris