Tuesday, March 27, 2012

aliasing two columns in SQL

Hi,

Here is my original query:

select rosterid, lastname, firstname from table
order by lastname

I would like to use column aliasing to display
lastname, firstname in a column entitled name.

I tried the following syntax, but it's not working:

select rosterid, lastname+', '+firstname as name
from table
order by name

This results in a 2 column table with the headings "ROSTERID" and
"NAME". However, NAME contains th last name only, rather than "lastname,
firstname".

Any help greatly appreciated.

Thanks,
Google Jeny

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!One thing worth looking at is not using reserved words such as Name.
You need to change this to something different.

R

Google Jenny <michiamo@.yahoo.com> wrote in message news:<412cfc29$0$14430$c397aba@.news.newsgroups.ws>...
> Hi,
> Here is my original query:
> select rosterid, lastname, firstname from table
> order by lastname
>
> I would like to use column aliasing to display
> lastname, firstname in a column entitled name.
> I tried the following syntax, but it's not working:
> select rosterid, lastname+', '+firstname as name
> from table
> order by name
> This results in a 2 column table with the headings "ROSTERID" and
> "NAME". However, NAME contains th last name only, rather than "lastname,
> firstname".
> Any help greatly appreciated.
> Thanks,
> Google Jeny
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Google Jenny <michiamo@.yahoo.com> wrote in message news:<412cfc29$0$14430$c397aba@.news.newsgroups.ws>...
> Hi,
> Here is my original query:
> select rosterid, lastname, firstname from table
> order by lastname
>
> I would like to use column aliasing to display
> lastname, firstname in a column entitled name.
> I tried the following syntax, but it's not working:
> select rosterid, lastname+', '+firstname as name
> from table
> order by name
> This results in a 2 column table with the headings "ROSTERID" and
> "NAME". However, NAME contains th last name only, rather than "lastname,
> firstname".
> Any help greatly appreciated.
> Thanks,
> Google Jeny
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

This query works for me in pubs:

select au_id, au_lname + ', ' + au_fname as 'name'
from authors
order by 'name'

Perhaps you could post your CREATE TABLE statement and some sample data?

Simon|||You may want to add the following as well to remove any additional
spaces in the name fields:

select rosterid, rtrim(lastname) +', ' + rtrim(firstname) as Name
from table|||Thanks so much. The rtrim did the trick.

Google Jenny

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Aliasing columns for a DMX subquery

I require the column of a nested table (KOL s) as part of the output of my DMX query, which needs to be written out to a relational table. Hence, I flatten the <select_list> of the SELECT DMX query as below:

SELECT FLATTENED

([Speciality].[SPECIALITY ID]) as [Speciality_Id],

(0) as [Bool_NameInAuthors],

(0) as [Bool_EmailInAbstract],

(0) as [Bool_AffiliationInAbstract],

(SELECT ([KOL ID]) as [Id], ([FIRST NAME]) as [FirstName], ([MIDDLE NAME]) as [MiddleName], ([LAST NAME]) as [LastName], ([AFFILIATION]) as [Affiliation], ([EMAILADDRESS]) as [EmailAddress] FROM [Speciality].[KO Ls]),

(SELECT ([Speciality Term DESCRIPTION]) as [Term] FROM [Speciality].[SPECIALITYTERMS]) AS Spec

From

[Speciality]

PREDICTION JOIN

OPENQUERY([ETL Profiler DB],

'SELECT

[SPECIALITY_ID]

FROM

[dbo].[KOLs]

') AS t

ON

[Speciality].[SPECIALITY ID] = t.[SPECIALITY_ID]

However, this causes the subquery columns (ID, FirstName, ...) to be aliased as Expression.ID, Expression.FirstName...

How do I alias these flattened columns properly?

I tried to alias the subquery to a derived table (as follows), but it just replaces the Expression word by the derived table alias (KOL in this case). So, does not solve my problem.

(SELECT ([KOL ID]) as [Id], ([FIRST NAME]) as [FirstName], ([MIDDLE NAME]) as [MiddleName], ([LAST NAME]) as [LastName], ([AFFILIATION]) as [Affiliation], ([EMAILADDRESS]) as [EmailAddress] FROM [Speciality].[KO Ls]) AS KOL

You can enclose the entire query in another SELECT where you alias the nested table columns -

SELECT [KOL.Id] as KOL_Id, [KOL.FirstName] as KOL_FirstName, ....

FROM

(SELECT FLATTENED ....) AS TT

sql

Aliasing a column name

Hi,
I'm trying to write a SQL event (view or storied proceedure). I have a table that is meant for reporting....the data is arranged verticle. I deal in Fiscal Years ie 2006/2007, 2007/2008, ect. The table in question has generic column lables ie FY1, FY2. I'm writing a report off the table and I want to dynamically turn FY1 into the current FiscalYear, FY2 into current FiscalYear + 1. I tried:

SELECT dbo.tblBudgetConfig.CurrentBudgetYear, dbo.tblBudgetProjectedCurrent.FY4 AS Left ([dbo.tblbudgetConfig.CurrentBudgetYear],4)+3 & "/" & Right([dbo.tblbudgetConfig.CurrentBudgetYear],4)+3
FROM dbo.tblBudgetConfig INNER JOIN
dbo.tblBudgetProjectedCurrent ON dbo.tblBudgetConfig.CurrentBudgetYear = dbo.tblBudgetProjectedCurrent.CurrentBudgetYear

But SQL squawks everytime I try this and tells me that there is something a miss near 'Left'.

Any help will be appreciated.

Nope, you cannot dynamically alias columns. Columns are part of the definition of the query and have to be there at the end of compile phase, not execution. To do this you would need to use dynamic SQL like EXEC ('query string'). You would have to build up the AS in a prior query .

A couple of things to note:

& does not work in SQL. You have to use + for concatenation (and you have to cast everything to the proper datatype_

"value" does not mean a literal, it means a column name. Use single quotes 'value'

Consider using the user interface to manage such things. This wouldn't be likely possible query anyhow because the data could change row by row, so you could then have a variable column name (it may not in your case, but SQL doesn't know that.) Instead of using the column names, add a column named fiscal year and store your value in there. Then let the UI put it in the right places and make it look all pretty for the user.

|||

AFAIK, you can′t compose the Aliases on the fly.

HTH, Jens K. Suessmeyer.


http://www.sqlserver2005.de

|||Thanks for your help. Its not the answer I was hoping for but what you are saying does make sense. And thanks for the t-sql tips too. I am really new at this so any help is appreciated.

Aliasing a column name

Hi,
I'm trying to write a SQL event (view or storied proceedure). I have a table that is meant for reporting....the data is arranged verticle. I deal in Fiscal Years ie 2006/2007, 2007/2008, ect. The table in question has generic column lables ie FY1, FY2. I'm writing a report off the table and I want to dynamically turn FY1 into the current FiscalYear, FY2 into current FiscalYear + 1. I tried:

SELECT dbo.tblBudgetConfig.CurrentBudgetYear, dbo.tblBudgetProjectedCurrent.FY4 AS Left ([dbo.tblbudgetConfig.CurrentBudgetYear],4)+3 & "/" & Right([dbo.tblbudgetConfig.CurrentBudgetYear],4)+3
FROM dbo.tblBudgetConfig INNER JOIN
dbo.tblBudgetProjectedCurrent ON dbo.tblBudgetConfig.CurrentBudgetYear = dbo.tblBudgetProjectedCurrent.CurrentBudgetYear

But SQL squawks everytime I try this and tells me that there is something a miss near 'Left'.

Any help will be appreciated.

Nope, you cannot dynamically alias columns. Columns are part of the definition of the query and have to be there at the end of compile phase, not execution. To do this you would need to use dynamic SQL like EXEC ('query string'). You would have to build up the AS in a prior query .

A couple of things to note:

& does not work in SQL. You have to use + for concatenation (and you have to cast everything to the proper datatype_

"value" does not mean a literal, it means a column name. Use single quotes 'value'

Consider using the user interface to manage such things. This wouldn't be likely possible query anyhow because the data could change row by row, so you could then have a variable column name (it may not in your case, but SQL doesn't know that.) Instead of using the column names, add a column named fiscal year and store your value in there. Then let the UI put it in the right places and make it look all pretty for the user.

|||

AFAIK, you can′t compose the Aliases on the fly.

HTH, Jens K. Suessmeyer.


http://www.sqlserver2005.de

|||Thanks for your help. Its not the answer I was hoping for but what you are saying does make sense. And thanks for the t-sql tips too. I am really new at this so any help is appreciated.

Aliases not found on log in

When loggining into SSMS there does not appear to be a way to connect to the
ALIAS remote servers names like you could with SQL Server 2000. Does any one
know how to use the Alias names at the SQL Server 2005 connection dialog?D. Haber (DHaber@.discussions.microsoft.com) writes:
> When loggining into SSMS there does not appear to be a way to connect to
> the ALIAS remote servers names like you could with SQL Server 2000. Does
> any one know how to use the Alias names at the SQL Server 2005
> connection dialog?
What aliases? Those you defined in the Client Network Utility? I guess you
need to use the SQL Configuration Manager to set up aliaes for the SQL 2005
tools. It's under SQL Native Client configuration, which is a little
misleading, but I did a very quick test, and it seemed to work.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Aliases & Columns Name w/ Spaces

Formatting question. The query below is failing on the columns with spaces in the name. I've tried brackets and single quotes with no luck. How should this be formatted?

-

SELECT x.trkuniq, s.mstuniq, t.meetuniq,
c.coursec AS Course_Code,
c.descript AS Course_Name,
[q.cactus #] AS Cactus_#
s.sectionn AS Section,
RTRIM(f.lastname) + ', ' + RTRIM(f.firstname) AS Teacher, f.funiq,
t.termc AS Term_Code, zd.cycldayc AS Day,
zp.periodc AS Period, zp.periodn,
p.schoolc AS School
FROM mstmeet t INNER JOIN
mstsched s ON t.mstuniq = s.mstuniq INNER JOIN
trkcrs x ON s.trkcrsuniq = x.trkcrsuniq INNER JOIN
course c ON x.crsuniq = c.crsuniq INNER JOIN
track p ON p.trkuniq = x.trkuniq INNER JOIN
facdemo f ON s.funiq = f.funiq INNER JOIN
courses q ON c.coursec = [q.course number] INNER JOIN
trkper zp ON t.periodn = zp.periodn AND
x.trkuniq = zp.trkuniq INNER JOIN
trkcycle zd ON t.cycldayn = zd.cycldayn AND
x.trkuniq = zd.trkuniq

Kinny:

Try changing your reference from [q.cactus #] to q.[cactus #]

Dave

|||

The brackets are use to encapsulate a name of a table, schema, etc, not the entire messed up name :)

You can even include dots in the name:

create table [dbo].[bad.idea]
(
[bad.idea.id] int
)

select *
from [bad.idea]

And yes, very very bad idea.

Aliases

I need to know where an alias is stored. I am new to SQL and need to
be sure I am able to recover from a server failure with all components
and jobs intact.
Thanks, JohnIn the registry under:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Client\ConnectTo
-Sue
On 30 Mar 2004 14:38:55 -0800, news13579@.hotmail.com
(Needing help as usual) wrote:

>I need to know where an alias is stored. I am new to SQL and need to
>be sure I am able to recover from a server failure with all components
>and jobs intact.
>Thanks, Johnsql