Inserting Serial No Column In Result Of A Query

Apr 21, 2005

Hello Friends

                     My problem is

                     Suppose Query is - Select * from tbl_Employee

                     TBL_EMPLOYEE HAS ONLY TWO COLUMNS NAME,POST

                     I need that an extra column get inserted in result through query showing serial Number with each row.So that query result look like this.

                     Serial   Name Post

                        1      XYZ   QER

                        2      SDF   OPO

                        3      WER   IPO

            If any body knows please post the solution its urgent.

View 11 Replies


ADVERTISEMENT

Same Query Gives Result With Different Column Sequence When Used In Query Analyzer

Feb 25, 2012

When I run query in excel it gives result with different column sequence. The same query gives result with different column sequence when used in query analyzer or VBA Macro. E.g., Select * from ABC.

result in Excel 2003 SQL OLE DB query

col-A col-B col-C
values...

Result with Query Analyzer and VBA Macro

col-c col-B col-A
values...

View 3 Replies View Related

Transact SQL :: SELECT On Column Name From Query Result Set In Same Query?

May 9, 2015

I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).

To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:

COLUMN_NAME Value
----------- -----
colB        123
colA        XYZ

I've tried dynamic SQL to no success, probably not executing the concept correctly...

Below is what I have:

CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC

[Code] ....

View 4 Replies View Related

Column Name As The Result Of A Query?

Jan 11, 2005

Simple example would look like that in MS SQL

SELECT 'a' AS (SELECT language_name FROM language WHERE language_id = 1)

So that the display is

English
a

as we assume that

SELECT language_name FROM language WHERE language_id = 1

returns only English

I have tried to that with a variable but it does not work

declare @that as varchar(15);
set @that = (select language_name_u from language where language_id = 1);
select 'a' as @that

LOL

I just tried another way as i was going to post

declare @that as varchar(15);
set @that = (select language_name_u from language where language_id = 1);
select 'a' as "@that"

and it worked!

Posting anyway so people might answer with a better solution with no variable

Thanks a lot

Mordan

View 1 Replies View Related

SQL 2012 :: Add Serial Number Column And Sno 1 To 40000

Mar 28, 2014

Add sno 1 to end of the coloum

I have table like ...

400000 laks rows in my table

name address id location
raju hyd 6789 hyd
vamshi vizag 9876 hashinapur

o/p like

I want insert sno coloum and sno 1 to 400000

sno name address id location
1 raju hyd 6789 hyd
2 vamshi vizag 9876 hasthinapur
3
4
5
6
..
.
.
.
.
40000

View 2 Replies View Related

Generate Serial Numbers And Update Column?

Jul 17, 2013

I have two tables. One table has empty column (ID) and I want to generate serial numbers in that column based two tables columns(LoanNum) condition.

Table A

ID LoanNum
Null 1234
Null 2345
Null 3456
Null 4567
Null 5678
Null 6789

Table B

LoanNum
1234
2345
3456
4567
5678
6789
2324
4352
4235

Id is not primary key, but should not duplicates keys, it is just a column want to generate serial number (1,2,3,4...etc) How to generate?

View 5 Replies View Related

Identity Column In Query Result

Jul 20, 2005

I am firing a query like:SELECT TaskName, StartDate FROMTasksWHERE StartDate >= '01-Aug-2003'Now the result comes as:TaskName StartDate--------------------------Task1 01-Aug-2003Task5 10-Oct-2003etc.Now what I want is this:Sl. No. TaskName StartDate----------------------------------1 Task1 01-Aug-20032 Task5 10-Oct-2003How do I get the Sl. No. field (it does not exist in the table).Can I do it?-surajit

View 11 Replies View Related

Store Query Result To Column

Nov 23, 2007

Hi,

I'm wondering is it possible to store query result to column directly?
for example I have 2 tables

Cache
----------
Cache_ID (int)
Cache_content (Text)
cache_created (Datetime)



Orders
-------------
Order_ID
Order_amount
Order_Date
Order_Delivered


and the query is
select * from order where order_date = '2007-11-23' and 'Order_delivered='N'

I want the result to be stored in cache table either csv or xml
I'm using sql 2000 server.

any idea?

View 7 Replies View Related

Return Result From One Query As A Column In Other Query

Jun 3, 2004

I'm having a bit of a trouble explaining what I'm trying to do here.

I have 3 "source" tables and a "connecting" table that I'm going to use

tblContacts - with contactID, ContactName etc
tblGroups - with GroupID, GroupName
tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups)

They are related in a table called
tblContactsGroupConnection - with ContactID, GroupID and SubGroupID

One contact can be related to many subgroups.
What I want is a list of all contacts, with their IDs, names and what groups they are related to:

ContactID, ContactName, [SubGroupName1, SubGroupName2, SubGroupName3]
ContactID, ContactName, [SubGroupName1, SubGroupName3]
ContactID, ContactName, [SubGroupName3]

I'm sure there's a simple solution to this, but I can't find it. Any help appreciated. :)

Kirikiri

View 1 Replies View Related

Adding Autonumber Column To A Query Result

Apr 23, 2004

I have a query that returns a list of results from the database and insert the results into a datatable. Is there any way to add a column to the query result that has autonumbers in it. for example the first record would have 1 the next 2, the next 3, and so on?

View 4 Replies View Related

Looping Through Query Result Column And PRINT To Log File....

May 24, 2007

i am creating a log file, and at 1 point I record how many rows are deleted after an update process.. I want to also record which productID are actually deleted... how would I write that?!

Basically I need know how I would get the list, and I am using PRINT command... I need to somehow write a loop that works through the list and PRINTS to the file....

Thanks in advance

View 1 Replies View Related

Derived Column Based On Result Of Oracle Query

Sep 18, 2007

Hi,

I need to create a derived column for each row in a SQL dataset.

This derived column needs to be created by passing across two values from the SQL dataset and querying an Oracle table based on those parameters. If the Oracle query returns a record(s) then the derived column should be set to 1 otherwise leave it as default (0).

One of these parameters needs to check a date range so I can't use a Lookup Transformation...any ideas how I can accomplish this ?


Thanks

View 5 Replies View Related

SQL Server 2012 :: How To Subtract A Column From A Query Result And Add Difference To Another

Mar 20, 2015

I have a query which provides the below result set:

1165 6
1,173.0013
9740 6
9820 13
2271 6
2287 13
10,952.006
11,029.0013
4,074.006
4,103.0013

I want to achieve something like below. It should subtract the '13' row to '6' row and provide another column with the result. the '6' and '13' category code share the same Key.

1165 6 -8.00
1,173.0013-8.00
9740 6 -80
9820 13 -80

[code]...

View 5 Replies View Related

Serial Number In Query, In MS Access And SQL Server

Jan 16, 2008

im trying to run a query, my first column should be serial number based on results, how can i do that for example

qry = "Select products, sum(prod_price), sum(prod_sellingprice) from sales group by products"

or

qry = "Select products, sellingperson from sales where seldate=#" & date() & "#"

i want first column of both queries as serial number based on results of query.

View 3 Replies View Related

Inserting Result Value Into A Table.

May 21, 2008

Hey guys I'm trying figure out if there is a way to insert a result from a stored procedure into a table without going through ASP.NET. This is just an experiment and for learning purposes since i did manage to work it in .NET.

For example.

CREATE PROCEDURE dbo.inserttoLog

@Modem_ID VARCHAR(7)

As

Select dbo.PRODUCT.MODEM_ID + ' ' + dbo.PRODUCT.COLOUR + dbo.PRODUCT.SPEED AS Part#
FROM PRODUCT
WHERE ([Part#] LIKE '%' + @Modem_ID + '%')


I want to take the concatenated result of PART# and insert it into a log table. Is there a way of doing that?

View 12 Replies View Related

Inserting Records From Result Set Into A Table

Nov 7, 2014

I am comparing names of people from a table without id field to a table that has those people along with their ids.

I have a select statements using different field combinations that fetches ids of these people and presents the result set like:

table1id,table1firstname,table2firstname,table1lastname,table2lastname

E.g.:

1 john john smith smith
1 john j smith smith
1 john jon smith smit

I want to be able to insert all the records from the result set into another table, like in the eg, but only excluding those ones that match all the above fields. How would I do this...

View 1 Replies View Related

Saving Query Result To A File , When View Result Got TLV Error

Feb 13, 2001

HI,
I ran a select * from customers where state ='va', this is the result...

(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes

I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record

Thanks for your help

Ali

View 1 Replies View Related

End Result Is Main Query Results Ordered By Nested Result

May 1, 2008

As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:

SHOWS
showID
showTitle

SHOWACCESS
showID
remoteID

VIDEOS
videoDate
showID

SQL is as follows:

SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;

I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?

thanks

View 3 Replies View Related

SQL Server 2008 :: Display A Column Alias As Part Of The Result Set Column Labels?

Feb 26, 2015

Is there a way to display a column alias as part of the result set column labels?

View 9 Replies View Related

Error: Column Name X Appears More Than Once In The Result Column List.

Oct 3, 2006

Hello,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: }

View 3 Replies View Related

Need Help On Getting Result From Row To Column

Jan 17, 2008

Hi
I need help to build the below data to my requirement
i have table having the following data's








NoofLive
Action
Sold
Ratio
EffDt

10-49
43
0
0
1/1/2008

50-99          
62
0
0
1/1/2008

100-199        
73
0
0
1/1/2008

200-299        
17
1
0.059
1/1/2008

300-499        
25
0
0
1/1/2008

500-999        
21
0
0
1/1/2008

1000+          
45
0
0
1/1/2008

100-199        
11
1
0.091
2/1/2008

1000+          
1
0
0
2/1/2008

100-199        
17
0
0
3/1/2008

500-999        
2
0
0
3/1/2008 
EffDt = (Jan-08,Feb-08,Mar-08)
I want creat result as follows from the above data's
















Jan-08
Feb-08
Mar-08

Eligible Lives
Quoted
Sold
Close Ratio
Quoted
Sold
Close Ratio
Quoted
Sold
Close Ratio

10 - 49
43
0
0
0
0
0
0
0
0

50 - 99
62
0
0
0
0
0
0
0
0

100 - 199
73
0
0
11
0
0
0
0
0

200 - 299
17
1
0.059
0
0
0
17
0
0

300 - 499
25
0
0
0
0
0
0
0
0

500 - 999
21
0
0
0
0
0
0
0
0

1000+
45
0
0
1
0
0
2
0
0
Please anybody can help build a query to generate the above result.
Thanks in advance
sthanika
 
 
 
 

View 1 Replies View Related

Inserting More Than One Column

May 30, 2006

I am trying to write a script to insert more than one column but the script I am making only inserts the column in the second line and I dont know why. 
In my code below, columnB is added to the table, but not columnA.  I am stumped
My sql is:
<CODE>
Alter table  [dbName].[dbo].[tableName] Add columnA bit Default 0 Not NullAlter table  [dbName].[dbo].[tableName] Add columnB bit Default 0 Not Null
</CODE>
thanks for your help.

View 1 Replies View Related

SP To Perform Query Based On Multiple Rows From Another Query's Result Set

Nov 7, 2007

I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w.  one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included)         INSERT INTO @users             SELECT u.userName,u.userID                 FROM  tableU u                LEFT JOIN tableW w ON w.workgroupID = u.workgroupID                WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is:             SELECT u.userName,u.userID
                FROM  tableU u
                LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
                WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!    

View 1 Replies View Related

Add Apostrophe To Column In Result Set

Feb 5, 2004

I have a brain teaser for you all.

The end result: I need one of the columns in my result set (col2) to have a preceeding apostrophe (i.e., '001234 ).

The problem: I am building my query as a string and then using the EXEC() function to execute it. For example:
SET @strSQL = 'SELECT col1, col2 FROM mytable'
EXEC(@strSQL)

Just to tame any answers that might not be directed to the specific question, the query Must be built as a string and then executed.

I have been unable to obtain the solution and I am wondering if it is even possible?

TIA

View 3 Replies View Related

Aggregate (SUM) A Column And Then Use The Result?

May 7, 2007

Hi,



I am importing some data from Excel. I have to SUM one of the columns, and then use the result of the sum to calculate the percentages of each row. How can I use the Aggregate to give me a total of a column, so that i can use the total in another task and use formulas to calculate the percentages? i have tried to use multicast and join, but I get an extra row with the sum, which is not what I want; I want to use the sum for all the data.



Thanks

View 9 Replies View Related

Re-display Result Set Without Re-running Query In Query Analyzer?

Apr 9, 2006

I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.

View 6 Replies View Related

Inserting Login Name Into Sql Column

May 20, 2007

I'm trying to INSERT into an Sql db column the User Name (Login Name) from a login to my website. So the insertparameters statement would be?
<insertparameters>
<asp:???????
</insertparameters>
Thank you for any help you can offer.

View 3 Replies View Related

Inserting Into Identity Column

Jan 2, 2001

Hi Friends

Happy new year 20001.

Is it requires DBO Permission for do 'SET IDENTITY_INSERT FARRentalUnit ON'
or Write permission is enough.
I don't want give dbo perivilage to user.

Please reply soon.

Thanks for reply in advance.


Murali

View 3 Replies View Related

Inserting Several Columns Into One Column

Mar 15, 2000

I would like to insert several columns into one column.
eg. Coloumn1, Column2 & Coloum3 insert into Column00

thanks, Vic

View 1 Replies View Related

Inserting Into Table With One Column 1,2,3,4...

Jan 14, 2005

Hi,

I have a temporary table into which I insert bulk data from a stored procedure output. It will be easy for me if the temptable can have a column with sequential numbers 1,2,3,4,. I could not figure out a way to do it. This is the stripped down sql what I have in the stored procedure.
insert into temptable select name, email, date from usertable where condition = xyz.
I tried auto increment column with seed 1, increment 1. But if I delete and insert again it does not start from 1. Is there any way to accompolish this? It will make the front-end code very simple.

Thank you

View 3 Replies View Related

Inserting A Unique Value In A Column

Oct 8, 2007

hi,
In the below code, i am trying to insert the identity value into the 2nd column of the Symp_Quote table. Schema of the table Symp_Quote is as follows.

CRAETE TABLE Symp_Quote
(
QuoteRevision_ID INT PK,
DocumentSeq TINYINT PK,
DocumentDisplayName VARCHAR (255)
)

1st column data is coming from the select statement. but for 2nd column i have to insert identity or some unique value. to do that i was trying withe the following code, but it failed.

INSERT INTO Symp_Quote
(
QuoteRevision_ID,
DocumentSeq,
DocumentDisplayName
)
SELECT
S.QuoteRevision_ID,
IDENTITY (INT,1,1) AS DocumentSeq,
T.Name,
FROM Symp S
INNER JOIN Trilogy T
ON
SQR.tril_gid = FQR.tril_gid



kindly correct me whr I am wrong.........


Thanks,
Rahul Jha

View 5 Replies View Related

Inserting A Value With Spaces Into A Column

Jun 17, 2008

Hello,

I am fairly new to SQL I have started to administer a system which handles carrier information for a mail order system. The logic behind the system is quite simple there are 5 or so columns in a table the first column is the first part of the postcode i.e EX15, the other columns contain which delivery services and depot numbers are associated with that postcode. It works fine at the moment.

However now the main carrier has decided that they are now going to split these postcodes so for example EX15 1* goes to a different depot than EX15 2*

I cant seem to insert EX15 1 into the first column, I get the following error:

Attempt to store duplicate value in unique column. (-155)

Is this because of the space in 'EX15 1'? Because 'EX15' already exists in that column? In which case do I need to somehow tell SQL that there is a space there?

I hope this makes sense

Below is a snap of the table with the existing EX15 postcode data

postcode|carrier_code|available|depot_code|hub_code|county_code
----------------------------------------------------------------
EX15.....|NF/S93......|........1|34........|C.......|DEVO
EX15.....|NF/SAT......|........1|34........|C.......|DEVO
EX15.....|NF/930......|........1|34........|C.......|DEVO
EX15.....|NF/AM.......|........1|34........|C.......|DEVO
EX15.....|NF/ON.......|........1|34........|C.......|DEVO
EX15.....|NF/48.......|........1|34........|C.......|DEVO
EX15.....|NF/3D.......|........1|34........|C.......|DEVO

Here is a snap of how I would like to set up another entry:

postcode|carrier_code|available|depot_code|hub_code|county_code
----------------------------------------------------------------
EX15 1...|NF/S93......|........1|34........|C.......|DEVO
EX15 1...|NF/SAT......|........1|34........|C.......|DEVO
EX15 1...|NF/930......|........1|34........|C.......|DEVO
EX15 1...|NF/AM.......|........1|34........|C.......|DEVO
EX15 1...|NF/ON.......|........1|34........|C.......|DEVO
EX15 1...|NF/48.......|........1|34........|C.......|DEVO
EX15 1...|NF/3D.......|........1|34........|C.......|DEVO

Thanks

View 3 Replies View Related

Inserting With Different Column Names

Feb 15, 2006

I'm trying to perform an insert on a table from another table, but
the fieldnames are different. The data is similar, but there are
less fields as well. Does anyone know how to accomplish this? All
the material I've read on the Internet so far pertaining to Insert
statements, has all the column names matching up.

Any help would be greatly appreciated.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved