How To Select All Columns Except One Column From A Table ?
Oct 6, 2006
Hi
I can't figure out how to do this and hope you guys can give me a hint....
I want to select all columns from a table except one column:
For example,
A table has 20 columns : {1,2,3.....20}
To select all of columns in a table, i could use
select * from table;
However, I want to select only column {1....19}
I do not want to type the column names in one by one in the SQL query,
is there a way to achieve this in a short form of Select?
Thanks,
View 15 Replies
ADVERTISEMENT
Oct 31, 2014
‘Trying to SELECT INTO a new table all columns of a table based on a DISTINCT value of one column so for example:
SELECT *
INTO new_table
FROM old_name
WHERE old_table.column IS DISTINCT’
View 4 Replies
View Related
Jun 17, 2015
I have a SQL query like this
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode, CurrencyCode,TransactionAmount order by CurrencyCode
As per this query I got the result like this
CurrencyCode TransactionCode TransactionAmount No.OfTrans
AED BNT 1 1
AED BNT 12 1
AED SCN 1 1
AED SNT 1 3
[Code] ....
But I wish to grt result as
CurrencyCode TransactionCode TransactionAmount No.OfTrans
AED BNT 13 2
AED SCN 1 1
AED SNT 11 7
AFN BPC 8 6
[Code] ....
I also tried this
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans]
from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date)
group by TransactionCode order by CurrencyCode
But of course this codes gives an error, but how can I get my desired result??
View 5 Replies
View Related
Apr 29, 2015
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
View 14 Replies
View Related
Feb 27, 2015
I have three records, with one column that is an identity/PK. I want the last record inserted and the whole record's information from the query, using a where clause to limit the record returned from the table.
I get an error if I do not have the group by added. However, with this query, I still get three records returned.
SELECT max([tablePK])
,[orderGUID]
,[tblPeopleFK]
,[tbl_shippingAddressFK]
,[tblBillingMethodFK]
,[shippingMethod]
[Code] ....
View 1 Replies
View Related
Nov 20, 2005
Hello all,
how can I select one or more columns from a table by column-index and NOT by columnname?
e.g.:
SELECT tbl1.[1], tbl1.[2], tbl1.[3] FROM Orders AS tbl1
and NOT like this:
SELECT tbl1.OrderNo, tbl1.ProductNo, tbl1.Price FROM Orders AS tbl1
Is that possible in MS-SQL 2000?
Thanks a lot in advance
kind regards
Otto
View 11 Replies
View Related
Oct 27, 2007
Hi I'd like to select two columns out of one column from another table. Example from source table:
Code Block
ID ArtNr EAN
1 4711 51323135
1 4712 84651213
1 4713 84351322
2 4711 86431313
2 4714 23546853
1 4714 54646545
2 4715 64684353
2 4716 65435132
I want to join this table to a base table with one select, and generate the following output. The ID identifies If the ArtNr has an EAN1 or EAN2:
Code Block
ArtNr EAN1 EAN2
4711 51323135 86431313
4712 84651213
4713 84351322
4714 54646545 23546853
4715 64684353
4716 65435132
View 20 Replies
View Related
Sep 27, 2006
For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".
can you tell me how can I complete this with the SQL?
View 2 Replies
View Related
Jan 20, 2014
I have two tables with the common column-name/ The first one include such columns as the name of football team, its country and budget. The second one include the team name, victories (quantity of it), lost games, and season. So I have the task to select such column as name, victories, lost games in 2nd table but only that ones that has budget over 1 mln? How to build select statement SELECT name, victories, lost g/ from TABLE2 WHERE budget >1000000 from TABLE1? Or should I equates the table1.name=table2.name??
View 6 Replies
View Related
Mar 11, 2008
Can anyone help me how to add a new column between a column in sql server 2005
i have did this one in SQL Server 2000 like below.
1. Add a new column using ALTER statement
2. EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
EXEC sp_configure 'allow updates',1
GO
RECONFIGURE WITH OVERRIDE
GO
3.Change the colid in syscolumns table using UPDATE Statement
- But its not working in SQL Server 2005...it throws an adhoc error for system catalog table
View 5 Replies
View Related
Oct 9, 2007
I'm wondering if there is a single statement I can write to pull my data. Let's say my Order table has one field for userId and one field for supervisorId (among other fields) both of which are foreign keys into the Users table where their name, address, etc. are stored. What I'd like to do is to pull all the rows from Order and have a join that pulls the user name and supervisor name from the User table all in one go. Right now I pull all the Orders with just user name joined, and then go back over the objects to add the supervisor name as a separate query.
The reason I'd like to do this is to simplify the objects I'm passing to the GridView by doing a single fetch instead of multiples. I'm using SQL Server, .NET 2.0 and VS.NET 2005.
Thanks
View 1 Replies
View Related
Mar 28, 2008
Hi everyone.
I am stuck on this for quite a while. Lets say i have 2 tables.
Table 1 with these columns:
Serial_Num
Product
Price
Table 2 with this column only:
Product
I need to create a VIEW that will show me all the the data in Table 1 but only for the column that exists in table 2. Example:
Something like this:
select (all the columns from table 2)
from table 1
I need it to be dynamic because columns could be added to both tables anytime.
Thanks.
View 5 Replies
View Related
May 9, 2008
I have a simple question that is more about performance on my code for updating a column with the sum of two columns:
Example
Table = dbo.adding
c1 c2 c3Sum
1 2
3 5
4 6
Now if I just simple write
Update dbo.adding
set c3SUM = c1 + c2
This works, but there is a null in c1 or c2 then I know I can write this:
if exists (select * from dbo.adding where c1 is NULL)
Update dbo.adding
set c1 = '0', c3SUM = '0' + c2 where c1 is NULL
if exists (select * from dbo.adding where c2 is NULL)
Update dbo.adding
set c2 = '0', equals = c1 + '0' where c2 is NULL
Update dbo.adding
set c3SUM = c1 + c2
This works as well, but I am not sure how perficient it is being that from my understanding the code will evaluate the table three times and update it based on the criteria. If the table consists of 100,000 rows then this might not be a good solution
Is there a more proficient way to do this... I personally try to as little coding as possible to help increase performance. I know I can make this very complex and declare variables and put everything in a loop, but I personally think that would be less profient..
View 8 Replies
View Related
Jan 2, 2004
i need help to get the value of a column which is selected from a column of another table....
View 3 Replies
View Related
May 30, 2006
Hi All,
With the OLEDB source, is it wrong to use a table / view as a source and only check the columns required or is it beneficial to write a select col1, col2 etc etc as a SQL command?
I cannot see any difference in performance between the two.
Thanks.
View 6 Replies
View Related
May 27, 2015
How can I pervert dropping the column in the table (probably some process doing that and I want to find it) ? I need to be able to modify but not alter / drop column .
View 4 Replies
View Related
Apr 6, 2008
Using SQL Server 2005 Express:
I'd like to know how to do a SELECT Query using the following tables:
Miles Table:
Date/Car/Miles/MilesTypeID
===============
(some date)/Ford/20/1
(some date)Ford/20/2
(some date)Chevy/30/1
(some date)Toyota/50/3
(some date)Ford/30/3
Miles Type Table
MilesTypeID/MilesType
=================
1/City
2/Highway
3/Off-Road
I'd like the results to be like this:
Date/Car/City Miles/Highway Miles/Off-Road Miles
=====================================
(date)-Ford-20-0-0
(date)-Chevy-0-20-0
(date)-Ford-20-0-0
(date)-Toyota-0-0-50
(date)-Ford-0-0-30
Anyone? Thanks in advance!
View 3 Replies
View Related
Aug 14, 2012
I have a table with a string value, where all values are seperated by a space/blank. I now want to use SQL to split all the values and insert them into a different table, which then later will result in deleting the old table, as soon as I got all values out from it.
Old Table:
Code:
ID, StringValue
New Table:
Code:
ID, Value1, Value2
Do note: Value1 is INT, Value2 is of nvarchar, hence Value2 can contain spaces... I just need to split on the FIRST space, then convert index[0] to int, and store index[1] as it is.
I can split on all spaces and just Select them all and add them like so: SELECT t.val1 + ' ' + t.val2... If I cant find the first space that is... I mean, first 2-10 characters in the string can be integer, but does not have to be.Shall probably do it in code instead of SQL?Now I want to run a query that selects the StringValue from OldTable, splits the string by ' ' (a blank) and then inserts them into New Table.
Code:
SELECT CASE CHARINDEX(' ', OldTable.stringvalue, 1)
WHEN 0 THEN OldTable.stringvalue
ELSE SUBSTRING(OldTable.stringvalue, 1, CHARINDEX(' ', OldTable.stringvalue, 1) - 1)
END
AS FirstWord
FROM OldTable
Found an example using strange things like CHARINDEX..But issue still remains, because the first word is of integer, or it does not have to be...If it isn't, there is not "first value", and the whole string shall be passed into "value2".How to detect if the very first character is of integer type?
Code:
@declare firstDigit int
IF ISNUMERIC(SUBSTRING(@postal,2,1) AS int) = 1
set @firstDigit = CAST(SUBSTRING(@postal,2,1) AS int)
ELSE
set @firstDigit = -1
[code]....
View 2 Replies
View Related
Feb 24, 2015
I have the following 2 Query's - case when Table has no Identity Column and other with identity Column . I am planning to make it to single Query .
Query 1:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE object_id =
(
SELECT sys.objects.object_id
FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing1' AND sys.schemas.Name ='dbo'
)
Query2:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE is_identity != 1 AND object_id =
(SELECT sys.objects.object_id FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing2' AND sys.schemas.Name ='dbo'
)
View 8 Replies
View Related
Oct 16, 2014
I am trying to pull a min value of a date column from table 1, if table 1 does not have any record I need pull a date column from table 2. Table 2 will always have a unique record ID (No duplicate ID's).
Table 1
ID date
1 1/1/2014
1 1/5/2014
Table 2
ID date
1 1/5/2014
2 10/15/2014
Here is the desired result when running for ID = 1 (select Min(date) where ID = 1, I should be getting 1/1/2014 from Table 1.
If I am running the same query where ID=2 then I should be getting 10/15/2014 from Table 2. So basically I need to check if a value exists on Table 1 first, if there is no value on Table 1 and then to grab the value from Table 2.
View 4 Replies
View Related
Jan 29, 2014
I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server. The issue I have is looking up a part number supersession and getting the latest number. One part number can have multiple supersessions (ie RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 ).The data I am supplied monthly has both the superseeded items and the supersession information in both columns and is not easy to decipher - for example:
Supersessions Table
----------------------
RTC5756 | STC9191
SFP500160 | STC9191
STC9191 | STC2951
STC3765 | STC9191
STC8572 | STC9191
STC9150 | STC9191
[code]...
The newest part number is kept in a separate table - called "source" - which in this instance is SFP500160. I need access to the latest part number but also to the part's previous numbers, due to the fact that some people may still be stocking them as an old part number and for them to search by. Is there an easy and efficient way of doing both a lookup for the supersessions and a join on the two tables to minimize the queries on the database?
View 9 Replies
View Related
Feb 26, 2015
I am using CROSS APPLY instead of UNPIVOT to unpivot > one column. I am wondering if I can dynamically replace column names based on different tables? The example code that I have working is based on the "Allergy" table. I have thirty more specialty tables to go. I'll show the working code first, then an example of another table's columns to show differences:
select [uplift specialty], [member po],[practice unit name], [final nomination status]
,[final uplift status], [final rank], [final uplift percentage]
,practiceID=row_number() over (partition by [practice unit name] order by Metricname)
,metricname,Metricvalue, metricpercentilerank
[code]....
Rheumatology Table:The columns that vary start with "GDR" and [GDR Percentile Rank] so I'm just showing those:
GDR (nvarchar(255), null)
GDR Percentile Rank (nvarchar(255), null)
GDR PGS (nvarchar(255), null)
GDR Rank Number (nvarchar(255), null)
PMPM (nvarchar(255), null)
[Code] ....
These are imported from an Excel Workbook so that's why all the columns with spaces for now.
View 9 Replies
View Related
Jul 31, 2015
I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.
CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT)
INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE
CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT)
INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE
One way to achieve this is to write two update statements. After update, the output you see is my desired output
UPDATE H
SET CHANNEL1= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=1 -- updates only channel1
UPDATE H
SET CHANNEL2= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=2 -- updates only channel2
SELECT * FROM #Hori -- this is desired output
my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.
View 5 Replies
View Related
Sep 6, 2005
Hello,I am passing a value to a stored procedure in vb.net to get normally aselection of the table. How could I get the complete table withoutwriting the code twice? Many thanks in advance ....
View 9 Replies
View Related
Nov 2, 2006
Hey, I'm just trying to do select top 1 * from lnksql1.db1.dbo.Table1. This table has a column which is an xml column. It returns an error saying "
Xml data type is not supported in distributed queries. Remote object 'LNKSQL1.db1.dbo.table1' has xml column(s).
"
What's the workaround for this? Thank you.
View 1 Replies
View Related
Nov 16, 2015
For our ETL process, we maintain a TransformationList table that has the source view and the destination table. Data is copied from the view into the table (INSERT INTO). I am trying to find column names in the Views that are not column names in the associated Table.
In the below example, want to end up with three records:
1, View1, Column4
2, View2, Column4
2, View2, Column5
I have it almost working, except that there is a table, ChangeColPrefix table, that is used by the ETL process to change some of the view's column name prefixes. Some of the source views have column names with prefixes that do not match the destination table column names. Say view SouthBase has all the column names prefixed with SB - like SBAcct, SBName. And the Destination table of Area District has ADAcct, ADName. There would be a row in the ChangeColPrefix for SouthBase, SB, AD, 1, 2 that would be used by the ETL process to create the INSERT INTO Area District From SouthBase.
I need to use this ChangeColPreifx to find my unmatching columns between my source views and destination tables. With out that table SBAcct and SBName from SouthBase will not appear to match the columns of ADAcct and ADName, but they do match.
I want to end up with these three records as non-matching:
View1, Column4
View2, Column4
View2, Column5
View1 has Salumn2 and View2 has Salumn5, and they must be changed to Column2 and Column5 as per the ChangeColPrefix table before running the Select from INFORMATION_SCHEMA.COLUMNS EXCEPT Select from INFORMATION_SCHEMA.COLUMNS looking for unmatched columns.
/***** Set Up Test Data *****/
-- Create 2 test views
IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View1]'))
DROP VIEW dbo.[View1]
GO
CREATE VIEW View1
AS SELECT '1' AS Column1 , '2' AS Salumn2 , '4' AS Column4;
[Code] ....
View 3 Replies
View Related
Jul 12, 2004
Hello fellow .net developers,
In a website I'm working on I need to be able to put all of the user tables in a database in a dropdownlist.
Another dropdownlist then will autopopulate itself with the names of all the columns from the table selected in the first dropdownlist.
So, what I need to know is: is there a sql statement that can return this type of information?
Example:
Table Names in Database: Customers, Suppliers
Columns in Customers Table: Name, Phone, Email, Address
I click on the word "Customers" in the first dropdownlist.
I then see the words "Name", "Phone", "Email", "Address" in the second dropdownlist.
I'm sure you all know this (but I'll say it anyways): I could hardcode this stuff in my code behind file, but that would be really annoying and if the table structure changes I would have to revise my code on the webpage. So any ideas on how to do this the right way would be really cool.
Thanks in advance,
Robert
View 5 Replies
View Related
Feb 10, 2007
Hello,
XPSP2
VB 2005 Express
Jet/Access database
One of the Forms of these application display a Jet database table. Why querries Min, Max, Avg do not work?
Thanks,
View 8 Replies
View Related
Dec 16, 2004
I have a table 'wRelated' with the following columns
[related_id] [int]
[channel_id] [int]
[mui] [varchar]
[price_group_id]
[type_id] [int]
[related_mui] [varchar] (100)
[date_started] [smalldatetime]
[date_ended] [smalldatetime]
[date_entered] [datetime]
[deleted] [tinyint],
[rank] [int]
data in column [mui] is repeated as the table has more than one entries for the same [mui],
The requirement is to select the distinct[mui] but value in all the other columns for the same mui should be select in the next row with null for the same [mui]
The recordset expected should be something like this.
[mui],[related_mui],[price_group_id],[date_entered],[date_ended] m123,rm345,'pr','12-10-2003',12-12-2004'
null,rm789,'ar','12-1-2003',26-2-2004'
null,rm999,'xy','14-12-2002',12-2-2004'
m777,rm889,'pr','12-12-2004',12-12-2004'
null,rm785,'yy','1-10-2002',12-12-2004'
m888,rm345,'pr','2-8-2003',12-12-2004'
null,rm345,'tt','30-7-2002',12-12-2004'
I have tried Unions and temporary table inserts.
View 1 Replies
View Related
Aug 16, 2004
Is there a SELECT statement that will return a the column names of a given table?
View 5 Replies
View Related
Aug 27, 2014
I'd like to first figure out the count of how many rows are not the Current Edition have the following:
Second I'd like to be able to select the primary key of all the rows involved
Third I'd like to select all the primary keys of just the rows not in the current edition
Not really sure how to describe this without making a dataset
CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,
[Code] .....
Group by fails me because I only want the groups where the Edition_fk don't match...
View 4 Replies
View Related
Jul 22, 2015
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3
**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
View 4 Replies
View Related
Feb 21, 2007
Hello, everyone:
I have an Excel table that has columns like,
Vendoe_Name
Level
Address
Email
Phone
I use the statemen
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C: est.xls;Extended Properties=Excel 8.0')...[Sheet1$]
and got output by column order,
Address
Email
Level
Phone
Vendoe_Name
How to make output by original column order like,
Vendoe_Name
Level
Address
Email
Phone
Any help will appreciated. Thanks
ZYT
View 1 Replies
View Related
