Table name as part of column name
-
Hi guys, Is there any way to have SQL Server return the table name as part of the column please? In my example, I would like to do something akin to... SELECT * FROM [Address] and have SQL Server return a result set containing columns with "Address." prepended to the column name. Address.ID, Address.Line1, Address.Line2 Many Thanks for any help you can provide!
-
Hi guys, Is there any way to have SQL Server return the table name as part of the column please? In my example, I would like to do something akin to... SELECT * FROM [Address] and have SQL Server return a result set containing columns with "Address." prepended to the column name. Address.ID, Address.Line1, Address.Line2 Many Thanks for any help you can provide!
Try this
select ID as 'Address.ID', Line1 as 'Address.Line1' ....
But why do you need it? Column names are unique in a select.
Bob Ashfield Consultants Ltd
-
Hi guys, Is there any way to have SQL Server return the table name as part of the column please? In my example, I would like to do something akin to... SELECT * FROM [Address] and have SQL Server return a result set containing columns with "Address." prepended to the column name. Address.ID, Address.Line1, Address.Line2 Many Thanks for any help you can provide!
-
Try this
select ID as 'Address.ID', Line1 as 'Address.Line1' ....
But why do you need it? Column names are unique in a select.
Bob Ashfield Consultants Ltd
Hi! Thank you for your reply. Unfortunately explicitly naming the columns is the only way I've found to do this so far. I would however like to be able to do something like SELECT * FROM Address and retrieve the columns as [Address].[name]. Do you know of a way of doing this without explicitly aliasing the column names please?
-
Hi guys, Is there any way to have SQL Server return the table name as part of the column please? In my example, I would like to do something akin to... SELECT * FROM [Address] and have SQL Server return a result set containing columns with "Address." prepended to the column name. Address.ID, Address.Line1, Address.Line2 Many Thanks for any help you can provide!
I'm attempting to build a DAL that will automatically create and populate objects from the results of a database call. As such, I need the data to be returned in column names that represent the object they are populating such as object.property e.g. Address.ID, Address.Line1 Sometimes I need just the property names e.g. ID, Line1, Line2 and other times I need Address.ID, Address.Line1, Address.Line2. It would be nice to have a view that I can call which returns the relevent fields and then I can simply prepend the object name to each of the fields when required.
-
Hi! Thank you for your reply. Unfortunately explicitly naming the columns is the only way I've found to do this so far. I would however like to be able to do something like SELECT * FROM Address and retrieve the columns as [Address].[name]. Do you know of a way of doing this without explicitly aliasing the column names please?
-
Sorry, no. I assume your query sometimes joins several tables, otherwise it would be a trivial thing to auto-generate your query.
Bob Ashfield Consultants Ltd
That is correct. In some instances I will be selecting fields from a view and would like to simply have SELECT * FROM vAddress AS Address which would output Address.ID, Address.Line1, Address.Line2 Thank you for your help. Looks like I'm gonna have to explicitly state the aliases in my procs. Cheers
-
I'm attempting to build a DAL that will automatically create and populate objects from the results of a database call. As such, I need the data to be returned in column names that represent the object they are populating such as object.property e.g. Address.ID, Address.Line1 Sometimes I need just the property names e.g. ID, Line1, Line2 and other times I need Address.ID, Address.Line1, Address.Line2. It would be nice to have a view that I can call which returns the relevent fields and then I can simply prepend the object name to each of the fields when required.
I dont know if this is helpful but here is an example of my customer DAL. I use Microsoft patterns and practices for SQL data access but you can just use SQL data adapter. I just dim cust as new DAL.Customer() cust.CustomerName = textbox1.text .... cust.save or cust.FetchCustomer(22) Imports Microsoft.VisualBasic Namespace DAL Public NotInheritable Class Customer #Region " Local Variables " Private _CustomerId As Integer Private _CustomerName As String Private _Address1 As String Private _Address2 As String Private _City As String Private _StateProvince As String Private _ZipPostal As String Private _CountryCode As String Private _DateCreated As Date Private _DateUpdated As Date Private _IsDirty As Boolean = False #End Region #Region " Public Properties " Public Property CustomerId() As Integer Get Return _CustomerId End Get Set(ByVal value As Integer) If value <> _CustomerId Then _IsDirty = True End If _CustomerId = value End Set End Property Public Property CustomerName() As String Get Return _CustomerName End Get Set(ByVal value As String) If value <> _CustomerName Then _IsDirty = True End If _CustomerName = value End Set End Property Public Property Address1() As String Get Return _Address1 End Get Set(ByVal value As String) If value <> _Address1 Then _IsDirty = True End If _Address1 = value End Set End Property Public Property Address2() As String Get Return _Address2 End Get Set(ByVal value As String) If value <> _Address2 Then _IsDirty = True End If _Address2 = value End Set End Property Public Property City() As String Get Return _City End Get Set(ByVal value As String) If value <> _City Then _
-
I dont know if this is helpful but here is an example of my customer DAL. I use Microsoft patterns and practices for SQL data access but you can just use SQL data adapter. I just dim cust as new DAL.Customer() cust.CustomerName = textbox1.text .... cust.save or cust.FetchCustomer(22) Imports Microsoft.VisualBasic Namespace DAL Public NotInheritable Class Customer #Region " Local Variables " Private _CustomerId As Integer Private _CustomerName As String Private _Address1 As String Private _Address2 As String Private _City As String Private _StateProvince As String Private _ZipPostal As String Private _CountryCode As String Private _DateCreated As Date Private _DateUpdated As Date Private _IsDirty As Boolean = False #End Region #Region " Public Properties " Public Property CustomerId() As Integer Get Return _CustomerId End Get Set(ByVal value As Integer) If value <> _CustomerId Then _IsDirty = True End If _CustomerId = value End Set End Property Public Property CustomerName() As String Get Return _CustomerName End Get Set(ByVal value As String) If value <> _CustomerName Then _IsDirty = True End If _CustomerName = value End Set End Property Public Property Address1() As String Get Return _Address1 End Get Set(ByVal value As String) If value <> _Address1 Then _IsDirty = True End If _Address1 = value End Set End Property Public Property Address2() As String Get Return _Address2 End Get Set(ByVal value As String) If value <> _Address2 Then _IsDirty = True End If _Address2 = value End Set End Property Public Property City() As String Get Return _City End Get Set(ByVal value As String) If value <> _City Then _
Hi, Thank you for your posting. This is essentially what I am doing with my DAL however am using reflection to make the DAL more generic across objects. As such, the column names in the returned data set must match the property names on the object. Additionally, my DAL is able to perform an eager-loading of sub-structures. It is for this eager loading that I need to have the table name returned as part of the column name e.g. Address -- ID -- Line1 -- Line2 -- Country -- -- ID -- -- Name -- Postcode By performing a join between the Address table and Country table, I can eagerly load the country information as a structure into the Address object. I just need to be able to identify the ID and Name fields as belonging the Country structure by prepending 'Country.' to the names. In the above example, it's fine to explicity alias the field names however I would like a solution which could perform something like... SELECT Address.ID AS ID, Address.Line1 AS Line1, Address.Line2 AS Line2, Country.*, Address.Postcode AS Postcode FROM Address LEFT JOIN Country etc... and which would rename the fields in Country to Country.ID and Country.Name. Obviously I could use a view for this however there may be times when I need the ID and Name property without the 'Country.' prefix. In such instances, it would be nice to reuse the same view instead of having a view that prepends the prefix and a view which does not.