Columns Differnt Values
Apr 1, 2008How can we write a script that will provide me colmumns and all differnt set of values for those columns...
there are 300 plus columns all together.
ADVERTISEMENT
Inserting Into Two Differnt Tables
Sep 21, 2007Hi, I have two different tables that I want to insert into, i have the code (as below) my only problem is i want to insert a qty into tbl_multi and a different qty into tbl_stock, how do i do this. I have been comfusing myself with it all afternoon, maybe someone can point it out for me int i = 0;
int scount = 0;for (i = 1; i <= 5; i++)
{if (((TextBox)FormView1.FindControl("txtP1_" + i.ToString())).Text != "")
{string sConnMAXID = "Data Source=sql380.mysite4now.com;Initial Catalog=nashdfDB1;User ID=******;Password=******";
SqlConnection objConnOID = new SqlConnection(sConnMAXID);int intStockIDMAX = (Convert.ToInt32(Session["intMAXSID"])) + 1;
int intQtyP1 = Convert.ToInt32(((TextBox)FormView1.FindControl("txtP1_" + i.ToString())).Text);int intLocationP1 = Convert.ToInt32(((DropDownList)FormView1.FindControl("ddlP1_" + i.ToString())).SelectedValue);
scount = intQtyP1 + scount;using (objConnOID)
{
objConnOID.Open();string sqlInsert = "INSERT INTO tbl_stock_multi_location(stock_ID, qty, location) " + "VALUES (@stock_ID, @qty, @location);"; <<<<<<<<<<<<<<<< need to try and add the SQL onto the end here
//INSERT INTO tbl_stock(qty)" +// "SELECT qty AS SQTY FROM tbl_stock VALUES (@SQTY);";
SqlCommand objCmd1 = new SqlCommand(sqlInsert, objConnOID);
objCmd1.Parameters.AddWithValue("@stock_ID", intStockIDMAX);
objCmd1.Parameters.AddWithValue("@qty", intQtyP1);objCmd1.Parameters.AddWithValue("@location", intLocationP1);
// objCmd1.Parameters.AddWithValue("@tbl_stock.qty", scount); <<<<<<< this was the param that i was playing about with
// either will work
// objCmd1.ExecuteNonQuery();
objCmd1.ExecuteScalar();
objConnOID.Close();
Rows From Two Differnt Databases
Jun 1, 2001how to pick rows from two tables in two differnt databases
View 1 Replies View RelatedReporting Services :: SSRS - Get Common Values Between Two Columns Where Values Sorted Comma Separated
May 6, 2015I have a situation in SSRS to get the common values between the two columns where the values are sorted comma separated as below.Ex:
ColumnA : abc,cde,efg
ColumnB : cde,xyz,abc
the result in
ColumnC : cde,abc
similarly Column A and B will have n number records. I need to right an expression or the Code function to get the required result in ColumnC. I am using SharePoint Lists as Datasource. Cannot write SQL query to achieve this requirement.
SQL 2012 :: Generating Date Range Values (start / End Dates) From Month Columns With Boolean Values
Jan 13, 2015I've got some records like this:
ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0
where each month field has a 0 or 1, depending on if the person was enrolled that month.
I'm being asked to generate a table like this:
ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014
Is there some slam dunk way to do this without a bunch of If/Then statements?
The editor compressed all my space fields, so the column headers are off in some places.
T-SQL (SS2K8) :: Converting Row Values To Columns With Dynamic Columns
Jun 11, 2015Basically, I'm given a daily schedule on two separate rows for shift 1 and shift 2 for the same employee, I'm trying to align both shifts in one row as shown below in 'My desired results' section.
Sample Data:
;WITH SampleData ([ColumnA], [ColumnB], [ColumnC], [ColumnD]) AS
(
SELECT 5060,'04/30/2015','05:30', '08:30'
UNION ALL SELECT 5060, '04/30/2015','13:30', '15:30'
UNION ALL SELECT 5060,'05/02/2015','05:30', '08:30'
UNION ALL SELECT 5060, '05/02/2015','13:30', '15:30'
[Code] ....
The results from the above are as follows:
columnAcolumnB SampleTitle1 SampleTitle2 SampleTitle3 SampleTitle4
506004/30/201505:30 NULL NULL NULL
506004/30/201513:30 15:30 NULL NULL
506005/02/201505:30 NULL NULL NULL
506005/02/201513:30 15:30 NULL NULL
My desired results with desired headers are as follows:
PERSONSTARTDATE STARTIME1 ENDTIME1 STARTTIME2 ENDTIME2
506004/30/2015 05:30 08:30 13:30 15:30
506005/02/2015 05:30 08:30 13:30 15:30
Union Of 2 Tables With Differnt Number Of Rows
May 24, 2006I have 2 tables
tblOpenSiteDates
Site: int
OpenDate: smalldate
Comment: VarChar
tblCloseSiteDates
Site: int
CloseDate: smalldate
Comments: VarChar
newLocationID: int
I am trying to get a view which would display a site with the open and close dates. Null is ok for a close date for those opens that are still open.. not every office has an open date and close date.. it is possible to have just a close date and not an open date (ie unsure of open date but I know its closing)
so the output would be
viewOpenCloseSites
Site: int
opendate: smalldate
closedate: smalldate
PLEASE HELP i just can not figure it out
thanks...
DTS Of Data Between Two Tables, Same Field Names But Differnt Datatypes
Jul 23, 2005I need to migrate data from one sql database to another. The second DBis a newer version of the "old" database with mostly the same tablesand fieldnames. In order support some reporting queries in the "new"version I needed to change the datatype of a few fields from varchar toint(the data stored was integers already as they were lookup tables).DTS works great except in the cases of about 10 fields which I changedthe datatypes on from varchar to int.DTS seems to drop the data if the fieldname and datatype are not anexact match. Is there any way to use DTS and have it copy data from afield call subsid type varchar to a field call subsid type int?
View 3 Replies View RelatedLookup To Check Values In 13 Columns With Same Values
Mar 22, 2006hi,
it is my first post on this forum, please be patient if i miss any important bit of information.
i am transporting data from a legacy system into mssql 2k5 using SSIS.
among those column of a dataset there are 13 columns, all necessary for operational reasons, that i need to ensure data consistance.
i believe i could do this check using the lookup data flow item, but surely there must be a way to do it in a more streamlined fashion.
since column names contain numbers to distinguish the version, eg; col01, col02, col03 .. col13.
i thought i could include the lookup within a loop and use a couple of variables to do this trick, but since i have not done it before i am asking for some sort of guidance from a guru among you folks.
please let me know if further clarification is necessary.
regards,
nicolas
Find Max Values From 3 Different Columns?
Mar 9, 2008Hi I want to find 3 different columns maximum values in one shot. Like I tried to use a reader to go through results but it kept coming back with index out of bounds. Right now to get it to work I got to use a ExecuteScalar() get the first columns max value and then open the connection again(since it seems after ExecuteScalar() it closes the connection) and then do the ExecuteScalar() again. Open the connection again and do the ExecuteScalar() again. Theres got to be a better way of doing this.
View 3 Replies View RelatedColumns With Multiple Values ??
May 23, 2006Hi,
The values I need to store in the table are
Student ID
Student Name
Subjects
The "Student ID" is the primary key.
A student can take more than 1 subject.
For example:
Student ID: 100
Student Name: Kelly Preston
Subjects: Geography, History, Math
How can I store these values in a database table?
I know the normal "INSERT" statement, but how would I store the multiple subjects for a single student ID?
My "Student ID" is auto generated. If I create a new row for each subject, the Student ID will be different for each subject, which I dont want.
Or I can create a new field called "RowNumber" and keep that the primary key..
For example:
Row Number StudentID StudentName Subject
1 100 Kelly Geography
2 100 Kelly History
3 100 Kelly Math
If this is the only way to store the multiple sibjects, then for a given student ID (say 100), how can I retreieve the associated name and subjects? What is the query for that?
Adding Values To Columns
Jan 17, 2012I have a table with 6 columns. which we can call a, b, c, d, e, f. What I want to achieve is to put data in column d and e and then split this result in column f.
The data I want to put in column d and e is already exported and executed from a table called exp_data, which is from a period of november.
So this is what i have so far but is not working:
update split_table set d =
select amount from exp_data
where period = '1111'
and exp_data.account = split_table.b
and exp_data.company = split_table.a
The error I get is incorrect syntax near select. Fixed the issue by adding a parenthesis before the select until the end...
Compare Values From Two Columns?
Sep 20, 2013I am trying to compare values from two columns but I don't know what I am doing, I tried select custom5,skipcount substr( custom5, 1, 4) as 'mynumber' from songs where custom5 > '0000%' where skipcount > cast( substr( custom5, 1, 4) as int ); But that is obviously wrong?
View 4 Replies View RelatedAdding Two Values From Two Columns
Sep 27, 2007Hi!
I have one table with thousands of records. Now, I have to add one more column to that table. And this new column will contain concatination of two columns in that same table. Is there any command to do this?
My table has two columns as 'file', 'extn', now i want to add 'fullname' column to that table, and it will contain the concatinated value of 'file' and 'extn'. If the 'file' field has one record as "myfile", 'extn' has one record as ".doc", I want to insert "myfile.doc" in the new column 'fullname'. Like this my table has thousands of records. How can i do this? Please help me! Thanks in advance!
Getting 0 Padded Values In The Columns.
Jul 20, 2005Getting 0 padded values in the columns.Hi All,I have a requirement to convert a integer to string and display it inSql server with fixed length say 3 chars. (in c, we wud use %03d inprintf)If the number is small say, 9 then it has to be displayed as 009,56 -> 056, 897-> 897, 6786 -> xxxChecked through STR and CAST functions, couldn't find any relevantparamters.if you have any ideas, please mail me.Thanks & Regards,Chandra Mohan
View 2 Replies View RelatedRow Values Display In Columns
Jun 5, 2015One Table have 2 Columns.
1.Order_number
2.PSON NUmber
In Table Contains 6 rows with 2 order_numbers and different PSON Number.I want Order Number,PSON1,PSON2,PSON3,PSON4 and its Values
Ex:--Order_number PSON NUmber
1 ONT10
1 ONT11
1 ONT12
2 ONT13
2 ONT14
I want data :- Order_number PSON1 PSON2 PSON3
1 ONT10 ONT11 ONT12
2 ONT13 ONT14 Null
Determining Null Values In Columns
Sep 7, 2004How to find out that there is a null value in a column rather than a valid integer, DateTime or bool value, for strings I use 'as' operator to cast the column value and it returns null when column value is null, but for value types using 'as' operator causes compile error and using simple casting causes runtime error, for example:
int count = (int)row["Count"];
and
int count = row["Count"] as int;
the first one throws an exception when Count is null and the second doesn't compile at all since 'as' applies to reference types, so what is the way other than exception handling to determine null value in a column?
How To Display The Values From Two Different Columns (was RS Question)
Jan 3, 2008Does somebody know how do you get a column in Reporting Services to display the values from two different columns? I don't mean a concat, but the result of the two columns.
This has been a headache for 2 days now.
Thanks!!
How To Replace Values In Different Columns At The Same Time
May 14, 2004HI,
I AM HAVING A TABLE WHICH HAS INCREMENTAL COLUMNS,WHERE COLUMNS GETS ADDED EVERY MONTH TO THE TABLE AND THE TABLE THEN CONTAINS PREVIOUS MONTH AND PRESENT MONTH DATA ABOUT CUSTOMERS ,DETAILS AND TRANSACTIONS.
THE PROBLEM WITH THIS DATA IS ,IF THE CUSTOMER IS NEW ,THEN IN PREVIOUS MONTHS HIS INFORMATION IS NULL,WHICH HAVE TO BE CODED HAS "NOT PRESENT".
NOW,
HOW DO WE CONVERT ALL THE PREVIOUS COLUMNS FOR A PARTICULAR CUSTOMER HAS NULL AT THE SAME TIME ?.
HERE IS HOW THE PROC WRITTENED FOR IT GOES :-
DROP PROCEDURE DE_NAT
CREATE PROCEDURE DE_NAT
AS
BEGIN
DECLARE @MONMIN1 NVARCHAR(100),MON NVARCHAR(100),@YEAR NVARCHAR(100) , @MONYEAR NVARCHAR(100)
SET @MONMIN1 = DATENAME((MONTH),DATEADD(MONTH,-1,GETDATE()))
SET @MON = MONTH(GETDATE())
SET @YEAR = YEAR(GETDATE())
SET @MONYEAR = @MON + @YEAR
EXEC('select A.CUSTOMERS,B.*,CAST(A.RFM_40D AS FLOAT) AS R40
INTO TSD_' + @MONYEAR
+ ' from TSD_20 A
LEFT OUTER JOIN SD20 ' + @MONMIN1 + ' B
ON A.CUSTOMERS = B.CUSTOMER')
END
THIS PROC JUST ADDS THE PRESENT MONTHS DATA TILL LAST MONTHS DATA.
BUT IF A CUSTOMER IS NEW, THEN HOW DO I REPLACE THE NULL VALUES FOR THE PREVIOUS DATA TO 'NOT PRESENT'
FOR EG :- IF THERE IS A NEW CUSTOMER ,HOW DO WE CHANGE :-
CUSTOMERS ERTYYTRE RTYUUYTR TYUIIUYT QWERREWQ DFGHHGFD
----------- ---------- ---------- ---------- ----------- ----------
101023 <NULL> <NULL> <NULL> <NULL> 1.0
102022 1.0 1.62.3 3.4 4.5
NOW, AS YOU CAN SEE, THAT FOR CUSTOMERS = '101023'.
THE COLUMN DFGHHGFD IS, THIS MONTHS DATA , I WANT TO CHANGE ALL NULL VALUES PRESIDING IT AS "INACTIVE"
CAN I CHANGE , ALL COLUMNS FROM NULL TO "INACTIVE" , AT THE SAME TIME. ?
AS NEXT MONTH, AGAIN THE COLUMNS IS GONNA INCREASE WHICH WILL
AGAIN CAUSE A PROBLEM .
PLS TELL ME A METHOD , SO THAT I CAN DO THE NEEDFUL.
T-SQL (SS2K8) :: How To Select Columns That Have Some Values Only
Jun 1, 2015I have event table that containing multiple events, and many of them are empty. I'd like to select only the columns that have values in them.
Here is an example:
IF OBJECT_ID('tempdb..#events') IS NOT NULL
DROP TABLE #events
create table #events (eventId int,
Category varchar(250),
events1 varchar(250),
[Code] .....
In this case, I'd like to run a query like this one(skip Column Event3):
Select eventId,Category,events1,events2,events4,events5 From #events
Divide Two Columns With Null Values
Aug 14, 2014I am trying to divide these two columns...
parsename('$'+ Convert(varchar,Convert(money,Q.[Product Cost]-R.[Shared Cost]),1),2) as [Pre Cost],
parsename('$'+ Convert(varchar,Convert(money,R.[New Product Cost]-R.[Shared Cost]),1),2) as [Post Cost]
Formula:
[Post Cost] / [Pre Cost]
Expected:
If [Post Cost] is null then I would need to assign 1 and divide by [Pre Cost] and similarly if [Pre Cost] is null then I would need to assign 1 so that I can divide [Post Cost] / [Pre Cost]
Trying To Filter Columns On Multiple Values With OR
Nov 6, 2014I am trying to filter my columns on multiple values. I need them all to be OR because I want it to look through all of the columns and wherever the value matches to not include in the view. My WHERE clause that I thought would work looked like this..
WHERE (NOT (RTPL_VOLUME_DATA_1.SYMBOL LIKE '%spot%')) AND (NOT (RTPL_VOLUME_DATA_1.ISSPREAD = 'True')) AND
(NOT (RTPL_VOLUME_DATA_1.GMIPRODUCTCODE = 'Internal')) AND (RTPL_VOLUME_DATA_1.TAG_COMMENT IN ('[]', '[Gscalp]', '[TT]', '[GX2]', '[NA]', '[STELLAR]')) OR
(RTPL_VOLUME_DATA_1.TAG_COMMENT IS NULL)
However this does not work and provide the data needed. I then thought that if I replaced all the AND's with OR's that would work, but here it does not filter anything.. not sure where to go from here.
Get Rows With Duplicate Values In Certain Columns
Jul 23, 2005Hi there,I would like to know how to get rows with duplicate values in certaincolumns. Let's say I have a table called "Songs" with the followingcolumns:artistalbumtitlegenretrackNow I would like to show the duplicate songs to the user. I considersongs that have the same artist and the same title to be the same song.Note: All columns do not have to be the same.How would I accomplish that with SQL in SQL Server?Thanks to everyone reading this. I hope somebody has an answer. I'vealready searched the whole newsgroups, but couldn't find the solution.
View 2 Replies View RelatedCount Of Null Values In Columns
Mar 16, 2006Hi,
I would like to ask how can I get count of null values in each column together?
I know I can use COUNT(*) - COUNT(<col>), but I need to explicitly name every column in the query.... is it possible without it?
Comparing 2 Columns Containing Null Values
Sep 28, 2006Hi All.
I'm having some issues with what seems to be a simple update statement but is giving me grief when one or both of the columns I'm comparing are null. My statement (simplified) is as follows:-
UPDATE
TAB_A
SET
TAB_A.TRADCODE = TAB_B.TRADCODE
FROM
TADS_STAGE.DBO.UNCLBRAMDEPT TAB_B
JOIN
TADS.DBO.UNCLBRAMDEPT TAB_A
ON
TAB_B.BRANCODE = TAB_A.BRANCODE
AND
TAB_B.MERDCODE = TAB_A.MERDCODE
AND
(
TAB_B.TRADCODE <> TAB_A.TRADCODE
)
If either of the TRADCODE fields (or both) are null then the comparison fails to return the row to update. I've tried setting the ANSI_NULLS setting to off, this has no effect, presumably because it will only work when comparing a column to a variable or evaluating if the column is null itself.
I've considered using ISNULL, but if one of the columns happens to contain the value that I specify as the replacement value then the comparison will result true and not include the row.
I'd be grateful for any pointers!
Thanks in advance
Handling Columns With Multiple Values
Jun 9, 2006I am writing a stored procedure that needs a access individual entries in a column with multiple entries delimited by a comma(yeah i know, not 1st NF) . Like this:
Key
NotANormalizedCol
1
1324, 5124, 5435,5467
2
423, 23, 5345
3
52334, 53443, 1224
4
12, 4, 1243,66
is there a function that returns a substring given a delimiter character? the only substring returning function that i found are the LEFT and RIGHT that returns fixed length substring.
I am pretty new to this, so I apologize if this is a trivial questions
Tranform Columns To Rows With Multiple Values
Sep 30, 2006I have the following result set:
Code:
NameCode1Value1Code2Value2
A1020020250
B20300NULLNULL
CNULLNULLNULLNULL
I want to transform the columns into rows like this:
Code:
NameCodeValue
A10200
A20250
B20300
Any suggestions?
Dynamically Delete Entire Columns' Values
Aug 3, 2004Hello all,
I was wondering if anyone knew of a way to dynamically delete all of the values for a group of columns. What I mean by this is that lets say a table (TableA) has five fields (Field1, Field2, Field3, Field4, and Field5) with 100 rows of data. I want to delete all of the data in Field1, Field2, and Field4. I do not want to delete any of the data in Field3 and Field5. I would then end up with a table with 5 fields and 100 row, but only 2 fields (Field3 and Field5) have data.
The catch is that I can't hardcode the field names of the fields I want to clear out (Field1, Field2, and Field4) into the SQL. This is because if any new fields are eventually added to the table I want them to be cleared out as well without modifying the SQL.
I can hardcode the field names of the fields that I want to keep values for (Field3 and Field5) in the SQL.
If anyone has any idea how to do this, I would greatly appreciate it.
Thanks in advance!
Query To Parse Out Values From One Column Into Different Columns
Feb 2, 2006I have a table where different types of values are stored in one field, but I need to seperate them into different fields based on a value in another field.
For (hypothetical) example:
There is an existing table with following info in three columns:
userid record recordtag
1 joe 1
1 j 2
1 jr 3
2 bob 1
2 a 2
2 sr 3
where recordtag indicates (1 for first name, 2 for middle initial, 3 for suffix)
I need to query these records for a report so it the output is:
userID firstname middleinitial suffix
1 joe j jr
2 bob a sr
What's the most efficient approach to create a query that will give me desired results? I have managed to create a very complex query that derives tables for each column I want to create and queries off of that derived table for the 'record' value based on the 'recordtag' values for a given 'userid'. The query is extremely slow, so I know there's some better way out there to get the results I want. Any help would be greatly appreciated. Thanks.
Adding Column With Null Values Between Two Columns
Mar 22, 2012If i have a table with Col1,Col2,Col4, and Col5, how can I create and add a Col3 with null values? The format would be varchar.
View 13 Replies View RelatedT-SQL (SS2K8) :: How To Cut Certain Values In A String To Separate Columns
Jun 5, 2014I have a column containing values for different languages. I want to cut out the values per languate in a seperat column.
The syntax is a 2 letter country code followed by : the value is contained in double quotes. each languate is separated by a ; (except for the last one)
EX ur English, Dutch and Swedish:US:"Project/Prescription sale";NL:"Project/specificatie";SW:"Objektsförsäljning"
The result would Be
column header US
with value Project/Prescription sale
next column header NL
with value Project/specificatie etc.
Here are table examples:
IF OBJECT_ID('[#SALETYPE]','U') IS NOT NULL
DROP TABLE [#SALETYPE]
CREATE TABLE [#SALETYPE](
[SaleType_Id] [int] NOT NULL,
[name] [nvarchar](239) NOT NULL,
[Code] ....
Values Of Two Columns In Two Different Tables--presentation Using Select
Mar 14, 2006Values of two columns in two different tables--presentation usingselectHi Everyone,i have two tables in the database . One is called address tableand one is adressPhone Table. Below is the sample of those two tablesAddresscol1 col2 col3X 12 13y 15 19z 18 10create table address(col1 varchar(20),col2 int, col3 int)insert into address values ('x',12,13)insert into address values ('y',15,19)insert into address values ('z',18,10)AddressPhoneCol4 Col5 Col613 213-455-9876 113 415-564-6546 213 543-987-5677 319 678-555-2222 1create table addressphone(col4 int, col5 varchar(50),col6 int)insert into addressphone values(13,'213-455-9876',1)insert into addressphone values(13,'415-564-6546',2)insert into addressphone values(13,'543-987-5677',3)insert into addressphone values(19,'678-555-2222',1)I have to display something like thisx 12 13 213-455-9876 415-564-6546 543-987-5677y 15 19 678-555-2222 NULL NULLSo there is one to many relationship between address andaddressPhone table where address.col3 = addressphone.col4I don't know how to write the query to get the phone numbers thathas he same id in the same row like I displyed above.I did something like this, but this is not workingselect col1,col2,col3, (select col5 from addressPhone where col6=1),(select col5 from addressPhone where col6=2), (select col5 fromaddressPhone where col6=3),from address table inner join addressPhoneon address.col3=addressphone.col4above is not working because it is complaining that a subquerycannot return multiple results. Col6 in the addressphone table isthe phone type ike business phone,mobile phone or home phone. It is possible that there are two phonenumbers for business phone.Please let me know how can I write this query.Any help will be greatly appreciated.Thanks
View 4 Replies View RelatedSELECT With NULL Values In One Or More Of Selected Columns
Apr 10, 2008Hello everyone,
I have a little problem here. I need to select data from multiple columns and multiple tables, some of those columns can have NULL value. I have tried few things, and havent find the right way so far. Problem that i am incountering is that when i run SELECT statment as it is right now, i dont get back results which match search parameters but have NULL value in one or more column in select statment. How can i fix this problem?
SELECT Person2role.person2roleID,Person.firstname, Person.lastname, Person.sex
FROM Person LEFT OUTER JOIN
Person2Role ON Person.personID = Person2Role.personID
WHERE (Person.firstname LIKE '%' + ISNULL(@firstname + '%', ''))
AND (Person.lastname LIKE '%' + ISNULL(@lastname + '%', ''))
AND (Person.sex = ISNULL(@sex, Person.sex))
This is a part of SELECT statment..just so you can get the idea of what i am doing..i have few more parameters
I am working with SQL Server 2005 Express Version
Thank you,
Alex