SQL Server 2008 :: Display A Column Alias As Part Of The Result Set Column Labels?
Feb 26, 2015Is there a way to display a column alias as part of the result set column labels?
View 9 Replies
ADVERTISEMENT
Select And Display Part Of Column
Dec 5, 2013How do I only display a part of data from the column ?
Lets say emp_id is E3456 and all i want to display is 3456 without that E?
Transact SQL :: Arithmetic Calculation Between Two Rows And Result In Alias Column?
Jul 12, 2015I am trying to compare Sales value of year 2015 with sales value of 2016 and the difference stored in alias column as Sales_growth for year 2016 , for year 2015 the alias column be as '1' similarly difference between margin of 2015 and 2016 and result stored in alias column as margin_rate in year 2016for 2015 as 1 but when there is no record for year 2015 and record present in 2016 for a given (month, SM,SG,CUST,SP) then the alias column sales_growth and margin_rate should be 100Â
Formula for calculation
SGR = (sales(2015)-sales (2016)) / Sales_growth(2015)
SGR = (3456.05-3603.33) /3456.05 = -0.043
MR =( margin (2015)-margin( 2016) / margin(2015)
MR = (1059.24-1053.07)/1059.24= 0.006
DECLARE @T1 TABLE
[code]....
last record : as there is no record  for year 2015 and record present in 2016 for a given (month, SM,SG,CUST,SP) then the alias column sales_growth and margin_rate should be 100
How To Display Result Of Different Year In Different Column Instead Of Different Row?
Feb 7, 2006I am trying to display component usage in January for the past year, if I want to display the year in different column, what should I do?
eg.
component_id component_description qty_used_on_2005_Jan qty_used_on_2006_Jan
C58B0BDD tape drive 2 3
Currently I am using this sql:
select cast(year(date_complete) as varchar(10)) + ' Jan' as Year, component_id, component_description,sum(component_qty) as total_qty_used
from view_jobComponent
where month(date_complete) = 1group by component_id, component_description, cast(year(date_complete) as varchar(10)) + ' Jan'order by component_id, component_description
which I will get something like this:
Year component_id component_description total_qty_used
2005 Jan C58B0BDD tape drive 22006 Jan C58B0BDD tape drive 3
Change Display Of Result Set From Showing As Rows To Column As One Or More
May 13, 2014I am interested in changing the way that data is displayed in my result set.Essentially I want to display a selection of rows (1 to n) as columns, the following diagram explains my intentions.Perhaps one of the greatest challenges here is the fact that I do not have a concrete number of rows (or BIN numbers). Each stock item could be stored in one or more BINS, which I will not know until running my query.
View 2 Replies View RelatedError Invalid Column Name (In Sqlserver 2005) While Giving Alias Column Name
Jan 15, 2008ALTER procedure [dbo].[MyPro](@StartRowIndex int,@MaximumRows int)
As
Begin
Declare @Sel Nvarchar(2000)set @Sel=N'Select *,Row_number() over(order by myId) as ROWNUM from MyFirstTable Where ROWNUM
Between ' + convert(nvarchar(15),@StartRowIndex) + ' and ('+ convert(nvarchar(15),@StartRowIndex) + '+' + convert(nvarchar(15),@MaximumRows) + ')-1'
print @Sel
Exec Sp_executesql @Sel
End
--Execute Mypro 1,4 --->>Here I Executed
Error
Select *,Row_number() over(order by myId) as ROWNUM from MyFirstTable Where ROWNUM
Between 1 and (1+4)-1
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ROWNUM'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ROWNUM
Procedure successfully created but giving error while Excuting'.
Please anybody give reply
Thanks
Transact SQL :: Calculate DateTime Column By Merging Values From Date Column And Only Time Part Of DateTime Column?
Aug 3, 2015How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies View RelatedSELECT Column Aliases: Refer To Alias In Another Column?
Apr 9, 2008Using SQL Server 2000. How can I refer to one alias in another column?E.g., (this a contrived example but you get the idea)SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM dataNote how the speed alias is used in the definition of acceleration alias but this doesn't seem to work.
View 11 Replies View RelatedSELECT Column Aliases: Refer To Alias In Another Column?
Apr 10, 2008Using SQL Server 2000. How can I refer to one alias in another column?
E.g., (this a contrived example but you get the idea)
SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM data
Note how the "speed" alias is used in the definition of "acceleration" alias but this doesn't work.
SQL Server 2008 :: Create Table / Set Default Column Value To Value Of Another Column?
Mar 11, 2015when creating a new table. How can I set the default value of the column to equal the value of another column in the same table?
View 5 Replies View RelatedSQL Server 2008 :: Error - The Column Delimiter For Column Was Not Found
Mar 26, 2010I am getting an error importing a csv file both using SSIS and SSMS. The csv is comma delimited with quotes for text qualifiers. The file gets partially loaded and then gives me an error stating The column delimiter for column "MyColumn" was not found. In SSIS it gives me the data row which is apparently causing the problem but when I look at the file in a text editor at the specific row identified the file has the comma delimiter and it looks fine. I am using SQL Server 2008.
View 9 Replies View RelatedSQL Server 2008 :: Storing Column Value To A Column
Sep 9, 2015I just have a question regarding storing values to a column in ms sql 2008.
Why is it that the value I inserted at the column is truncated when selected in a query.
The column for this is created to accept max. values.
-> Message VARCHAR(MAX) NULL
The string which I need to insert is a combination of characters with a length of 14,720.
According to some forums, the max value that a column can hold is 8000 chars. only (Is this true? even though I set it to MAX?)
How To Return Months For Column Labels In Crosstab
Aug 1, 2013I want to line up across the top of a cross tab Jan-12 Feb-12 ... July-13 Aug-13 up to and including the latest month in the db. What's the best way to achieve this?
I currently create crosstabs with case statements as follows:
select
sum(case month(date) when 1 then value) as Jan,
sum(case month(date) when 2 then value) as Feb
from db
I figured you could create some sort of while loop for the case statements but I can't get the syntax right.
Or another strategy would be to use the Pivot method and build [Jan], [Feb], [Mar] etc through a loop?
Error: Column Name X Appears More Than Once In The Result Column List.
Oct 3, 2006Hello,I am trying to follow along with the Data Access tutorial under the the "Learn->Videos" section of this website, however I am running into an error when I try to use the "Edit -> Update" function of the Details View form: I'll post the error below. Any clues on how to fix this? Thanks in advance!!! ~DerrickColumn name 'Assigned_To' appears more than once in the result column list. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Column name 'Assigned_To' appears more than once in the result column list.Source Error: Line 1444: }
Line 1445: try {
Line 1446: int returnValue = this.Adapter.UpdateCommand.ExecuteNonQuery();
Line 1447: return returnValue;
Line 1448: }
How Can I Send Row Data To A SQL Database Table Via VB 2008, And Permanently Save And Display The Result?
Mar 11, 2008 This is related to:
How can I make some graphics drawings stick while others disappear?
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2905460&SiteID=1
Except that now I am trying to connect and update to an Microsoft SQL Server Database File (SqlClient) via VB 2008 Express; specifically a table called €œHexMap€? that contains some columns that I am ready to insert some row data into. Here is what my program should do:
As I hover over a hexagon map of the US a red flickering hexagon follows the location of my mouse cursor. If I click on a given hexagon, the program draws a permanent blue hexagon, and sends a new set of row data into my database. Such information as the name of the state, row, column, center x, and center y, etc. Here is a quick snapshot of this program in action:
http://farm4.static.flickr.com/3128/2325675990_4155edbdee_o.jpg
-sorry, I didn't capture the mouse cursor inside the red hexagon
I think I am missing something since I appear to be able to connect successfully to the database table. Unfortunately, I never see the changes in the database, when I try to Show Table Data (via Database Explorer). I am hoping someone will review my code snippet (below) and tell me what I am missing. What happens when I run this code is that it acts like it works just fine, except that I have no indication that any changes were actually affected.
Code Snippet
'======================================================================================
Dim CN As New SqlClient.SqlConnection()
Dim da As New SqlClient.SqlDataAdapter
'Consider using Me._adapter that is used already
CN.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Mapboard.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
CN.Open()
'Use the following code to verify that a connection to the database has achieved
If CN.State = ConnectionState.Open Then
MsgBox("Workstation " & CN.WorkstationId & "connected to database " & CN.Database & "on the " & CN.DataSource & " server")
End If
Try
Catch ex As Exception MsgBox("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR" & vbCrLf & ex.Message)
End Try
'use the Connection object to execute statements
'against the database and then close the connection
da = New SqlClient.SqlDataAdapter("select * from HexMap order by Territory", CN)
If CN.State = ConnectionState.Open Then CN.Close()
'==========================================================================
Dim rows As Integer
rows = 0
Dim CMD As New SqlCommand("INSERT HexMap (Hexagon, HexRow, HexCol, HexX, HexY, Territory) VALUES(HexCounter, CaptureRow,CaptureCol,Hx,Hy,Territory_ComboBox1.Text)", CN)
CN.Open()
rows = CMD.ExecuteNonQuery
If rows = 1 Then
MsgBox("Table HexMap updated successfully")
Else
MsgBox("Failed to update the HexMap table")
End If
If CN.State = ConnectionState.Open Then CN.Close()
'==========================================================================
Thanks for reviewing my code.
Technozoide
SQL Server 2012 :: Duplicate Of Part Num To Be Summed And Add Another Column Called User
Aug 25, 2015There are 3 columns in the result set - part num, Qty and MO num. Each MO num has part numbers.So there might be same part numbers in MO's. Each part num has qty. So, if I group by part num, I get Qty.
1.There are duplicates of part.num and I want to remove duplicates and add quantities of those duplicates into one single quantity. For example, xxxx is a part num, then xxxx=1,xxxx=3,xxxx=5. I want xxxx=9. I want to sum those. Another question is, each MO has a user. I want to join user and MO num in MO.
Heres the code,
part.num , (woitem.qtytarget/wo.qtytarget) AS woitemqty,
(SELECT LIST(wo.num, ',') FROM wo INNER JOIN moitem ON wo.moitemid = moitem.id WHERE moitem.moid = mo.id) AS wonums FROM mo INNER JOIN moitem ON mo.id = moitem.moid
LEFT JOIN wo ON moitem.id = wo.moitemid
LEFT JOIN woitem ON wo.id = woitem.woid AND woitem.typeid = 10 LEFT JOIN (Select sum(woitem.qtytarget) as labor, woitem.woid, uom.code as uom from woitem JOIN part on woitem.partid = part.id and part.typeid = 21 JOIN uom on woitem.uomid = uom.id group by 2,3) as labor on wo.id = labor.woid LEFT JOIN part ON woitem.partid = part.id
Several Alias For The Same Column
Mar 2, 2005In SQLServer I can't change my SQL column name.
But I need to see another field name in query tool (Excel)
How can I do that (alias..., description ?
Thanks
Column Alias
Aug 30, 2006Hello everyone.
I was wondering if there is a way that you can set the alias name of a column to a value that resides in another table instead of the alias being a static value that you type in.
If anyone has any ideas on ho i can accomplish this i would greatly appreciate it.
SQL Server 2014 :: Duplicates Of Part-number To Be Summed And Add Another Column Called User?
Aug 25, 2015There are 3 columns in the result set - part num, Qty and MO num. Each MO num has part numbers.So there might be same part numbers in MO's. Each part num has qty. So, if I group by part num, I get Qty.
1.There are duplicates of part.num and I want to remove duplicates and add quantities of those duplicates into one single quantity. For example, xxxx is a part num, then xxxx=1,xxxx=3,xxxx=5. I want xxxx=9. I want to sum those. Another question is, each MO has a user. I want to join user and MO num in MO.
Heres the code,
part.num , (woitem.qtytarget/wo.qtytarget) AS woitemqty,
(SELECT LIST(wo.num, ',') FROM wo INNER JOIN moitem ON wo.moitemid = moitem.id WHERE moitem.moid = mo.id) AS wonums FROM mo INNER JOIN moitem ON mo.id = moitem.moid
LEFT JOIN wo ON moitem.id = wo.moitemid
LEFT JOIN woitem ON wo.id = woitem.woid AND woitem.typeid = 10 LEFT JOIN (Select sum(woitem.qtytarget) as labor, woitem.woid, uom.code as uom from woitem JOIN part on woitem.partid = part.id and part.typeid = 21 JOIN uom on woitem.uomid = uom.id group by 2,3) as labor on wo.id = labor.woid LEFT JOIN part ON woitem.partid = part.id
Order By Column Alias
Feb 12, 2007I'm using SQL Server 2005 and are having some troubble with sorting a paged result set. I'm using the OVER Clause to achieve the sorting and paging and have the following query:1 WITH ProjectList AS
2 (
3 SELECT
4 Id,
5 Name,
6 Created,
7 (SELECT COUNT(*) FROM UserProjects WHERE ProjectId = p.Id) AS NumberOfUsers,
8 ROW_NUMBER() OVER (ORDER BY Id) AS 'RowNumber'
9 FROM Projects p
10 )
11 SELECT *
12 FROM ProjectList
13 WHERE RowNumber BETWEEN 50 AND 60;
This works fine, and give me the results i want. The problem occurs when I want to sort by "NumberOfUsers" which is the results of a sub query.When i say "ORDER BY NumberOfUsers" instead of Id on line 8, I get the following error:
Msg 207, Level 16, State 1, Line 10Invalid column name 'NumberOfUsers'.
I read this in the documentation:
When used in the context of a ranking window function, <ORDER BY Clause> can only refer to columns made available by the FROM clause. An integer cannot be specified to represent the position of the name or alias of a column in the select list. <ORDER BY Clause> cannot be used with aggregate window functions.
So this means that what I'm trying to do is not possible. How can I then sort by NumberOfUsers? Is there any other way to achieve this
Column Alias In Views
Oct 31, 2005Hi All,
I am currently transferring my Access application to SQL Server. Access allows you to declare and use aliases in the query at the same time.
e.g.
Select field1 as Alias1, field2 as Alias2, Alias1 & " " & Alias2 as Alias3 from table1;
In Access the above query will execute perfectly, no problem. However in SQL Server, if you try to run the same query it will give an error "Invalid column name Alias1" meaning that SQL Server is searching for Alias1 as a field in the table, not as an alias from the query.
My question is does SQL Server have a facility to declare and use alias directly as in Access and if no, is there a workaround?
Thanks for your time.
Regards:
Prathmesh
Using Column Alias In SELECT (was Asking)
Nov 25, 2006Hi,
I have a question.
select name, count, 1 Aa c1, 2 as c2, c1+c2 As total
from table1
From this example, the program will give out error message,
c1, and c2 are invalid columns.
In MS Access, it works. But, SQL query Analayer doesn't work this statement.
So, does query analayzer handle this case?
Thanks.
Column Name Alias Concatenation
Feb 20, 2004I have a web application where I would like to return a dynamic column name using aliasing. below is an example:
select hours as 'Fri<BR>' + cast(Day(getDate()) as varchar(2)) from todayshours
I get an error trying to do concatenation as part of the alais. Any ideas?
Luke
lgraunke AT 4invie.com
Use Column Alias In Another Calculation
Jul 20, 2005Is there a way to use a column alias in an another calculation within thesame query? Since I am using some long and complex logic to compute total1and total2, I don't want to repeat the same logic to compute the ratio ofthose two columns. I know that I can do a nested query, but that seems toolengthy as well since I actually have many, many columns.selecttotal1 = sum(case(long complex logic)),total2 = sum(case(another long complex logic)),ratio = total1/total2
View 6 Replies View RelatedColumn Alias As Variable
Jun 28, 2006Is there a way to select a column as an alias using a variable for the alias? something like this:
SELECT Column1 as @myVariable FROM Table1
Multi Column Subquery? W/ Alias
Jun 12, 2008What I need to do is to create 3 columns with 3 different aliases from the same table that will return all the values during the following conditions:
when pricelist = 1
when pricelist = 2
when pricelist = 3
pricelist
--------
1
2
3
Price
--------
912 -- (linked with 1)
234 -- (linked with 3)
56 -- (linked with 2)
3245 -- (linked with 3)
234 -- (linked with 1)
65 -- (linked with 2)
these 2 columns are in the same table^^
so what i want my query to generate is:
Price1
--------
912
234
Price2
--------
56
65
Price3
--------
234
3245
Any help is apprecieated, thanks
if the above does not make sense to you maybe this will:
"can you make 3 aliases of the same column and only display the rows inside each column where pricelist = 1 for the 1st alias... where price = 2 for the 2nd alias...where pricelist = 3 for the 3rd alias"
Using Alias Column Names For Calculations
Oct 3, 2007Hi,
I want to have a query where in i can use alias column names in the same query.
like eg
select 1 as 'a', 2 as 'b', a+b as 'c'
note that this query is getting big and is using sub queries.
Kindly help.
Thanks
Select Alias -- Invalid Column Name
Jun 26, 2007Hi,I got 'Invalid Column Name NewCol1' when I query the following:Select col1, col2, (some calculation from the fields) as NewCol1,(some calculation from the fields) as NewCol2,NewCol1 = NewCol2 fromTable1 inner join Table2 inner join Table3....Where.....Basically, I want to find out if NewCol1 = NewCol2 after thecalculationAny advice?Thanks in advance. Your help would be greatly appreciated.Wanda
View 5 Replies View RelatedHelp...!! Can We Compare Alias Of Column Within The Same Query??
Feb 22, 2007Can We Compare alias of column(Derived column) within the same query??
Ex:
Select (abc+50)*100 as 'WXY' from XYZ where WXY>150
.....
I cant execute such statement ... Can anyone help me how to comapre the alias within the same query..?
SQL Server 2012 :: Display Data In Blocks (Column Name)
Jan 29, 2014I want to display data in block. Below is the Create,Insert Script and format of desired output.
CREATE TABLE [dbo].[Test_AA](
[CounterpartID] [varchar](2000) NULL,
[CounterpartName] [varchar](2000) NULL,
[SATName] [varchar](2000) NULL,
[NameOfBO] [varchar](2000) NULL,
[Code] ....
Result Should be like This :
ID:17388
Name:Scottie Berwick
SATName:Scottie Berwick
NameOfBO:
SATAccountNumber:
[Code] ....
SQL Server 2014 :: Display Column Headers And Rows From Different Tables?
Dec 18, 2014I am looking for SQL query which uses 2 tables CASH and BALANCE.
eg: Need Tablename, ColumsList and data in the results set.
eg: 10 rows shown below and ordered based on Acct_number
Row1,CASH,ACCT_NUMBER,AMOUNT,DEBIT_CREDIT_FLAG,ENTITY,BUSINESS_DATE,CURRENCY,REFERENCE,TRADE_TYPE,SUB ACC CODE
Row2,BALANCE,ACCT_NUMBER,OPENING_BALANCE,CLOSING_BALANCE,CLOSING_BAL_DEBIT_CREDIT_FLAG,BUSINESS_DATE,CURRENCY
Row3,CASH,10,500,CR,ABC,12/12/2014,USD,INTL,,US05
Row4,CASH,10,1000,DR,DEF,12/12/2014,USD,DOM,,US07
Row5,CASH,10,75,DR,XYZ,12/12/2014,USD,DOM,,US05
Row6,BALANCE,10,500,750,DR,12/12/2014,USD
Row7,CASH,20,500,CR,ABC,12/12/2014,USD,INTL,,US05
Row8,CASH,20,1000,DR,DEF,12/12/2014,USD,DOM,,US07
Row9,CASH,20,75,DR,XYZ,12/12/2014,USD,DOM,,US05
Row10,BALANCE,20,500,750,DR,12/12/2014,USD
Dataset Query With Alias Column And Allow Searches
Jul 13, 2005I have a form that loads a dataset. This dataset is composed from SQL statements using alias and unions. Basically it takes uses data from 3 tables. This dataset also has a alias column called ClientName that consists of either people's name or business name.In addition, the form also consist of a search field that allows user to enter the 'ClientName' to be searched (i.e. to search the alias column). So, my question is how can the alias column be searched (user can also enter % in the search field)Function QueryByService(ByVal searchClientNameText As String) As System.Data.DataSet
If InStr(Trim(searchClientNameText), "%")>0 Then searchStatement = "WHERE ClientName LIKE '" & searchClientNameText & "'"Else searchStatement = "WHERE ClientName = @searchClientNameText"End If
Dim queryString As String = "SELECT RTrim([People].[Given_Name])"& _"+ ' ' + RTrim([People].[Family_Name]) AS ClientName, [Event].[NumEvents],"& _"[Event].[Event_Ref]"& _"FROM [Event] INNER JOIN [People] ON [Event].[APP_Person_ID] = [People].[APP_Person_ID]"& _searchStatement + " "& _"UNION SELECT [Bus].[Organisation_Name],"& _"[Event].[NumEvents], [Event].[Event_Ref]"& _"FROM [Bus] INNER JOIN [Event] ON [Bus].[APP_Organisation_ID] = [Event].[APP_Organisation_ID] "& _searchStatement
..........End Function
The Column Prefix 'MS1' Does Not Match With A Table Name Or Alias Name Used In The...
Aug 29, 2007I have an Access database, that is in connection with sql server.
On my computer with access2007 i have no problems with viewing tables and stuff.
But on other computers with access2003 it gives an error when i use this query:
INSERT INTO [tbl_sap-staffel] ( ItemCode, CardCode, [Amount-0], [Price-0], [Amount-1], [Price-1], [Amount-2], [Price-2] )
SELECT [qry_sap-spp-0].ItemCode, [qry_sap-spp-0].CardCode, [qry_sap-spp-0].Amount, [qry_sap-spp-0].Price, [qry_sap-spp-1].Amount, [qry_sap-spp-1].Price, [qry_sap-spp-2].Amount, [qry_sap-spp-2].Price
FROM ([qry_sap-spp-0] LEFT JOIN [qry_sap-spp-1] ON ([qry_sap-spp-0].ItemCode = [qry_sap-spp-1].ItemCode) AND ([qry_sap-spp-0].CardCode = [qry_sap-spp-1].CardCode)) LEFT JOIN [qry_sap-spp-2] ON ([qry_sap-spp-1].ItemCode = [qry_sap-spp-2].ItemCode) AND ([qry_sap-spp-1].CardCode = [qry_sap-spp-2].CardCode);
It says:
ODBC call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]
The column prefix 'MS1' does not match with a table name or alias name used in the query.
The column prefix 'MS2' does not match with a table name or alias name used in the query.
And it will give this error 6 times.
I dont know what to do.
Can anybody help me?