Procedure Or Query To Make A Comma-separated String From One Table And Update Another Table's Field With This String.
We have the following two tables :
Link ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Comma Separated String To Int
I have a checkbox list on datalist as one column. when user selects more than one checkbox and click on apply. i concatenate IDs of checkboxes as '1,2'3' for e.g. and sending that to Stroe Procedure as varchar datatype parametrer. In Procedure i wanna update status of all three selected and i am using statement "update tbl set status=1 where pageid in('1,2,3'). It is saying it cannot convert varchar to int. How can i do this task? Thanks in advance.
View Replies !
View Related
Sql Statement Comma Separated String
I have a table called evidence, which has the following Fields | evidence_id | Description| Standards| E001 blagh 1.1,1.2,1.3 Ok I am trying to search the comma-separated string in the standards field using the like clause so I can display the evidence_id. SQL looks like SELECT Evidence.Standards, * FROM Evidence WHERE (((Evidence.Standards) Like '%1.1%')); However it will not search through the list and select for example if I change 1.1 to 1.2. The commas wont allow it. It works if I just have one item in the list that is just 1.1. Can anyone help me to search a comma-separated string for a certain string? Thanks Asylum
View Replies !
View Related
Concatenated String Of Comma Separated Values (was &"Help With Query&")
I have following 2 queries which return different results. declare @accountIdListTemp varchar(max) SELECT COALESCE(@accountIdListTemp + ',','') + CONVERT(VARCHAR(10),acct_id) FROM (SELECT Distinct acct_id FROM SomeTable) Result print @accountIdListTemp The above query return the values without concatenating it. declare @pot_commaSeperatedList varchar(max) SELECT DISTINCT acct_id into #accountIdListTemp FROM SomeTable SELECT @pot_commaSeperatedList = COALESCE(@pot_commaSeperatedList + ',','') + CONVERT(VARCHAR(100),acct_id) FROM #accountIdListTemp print @pot_commaSeperatedList drop table #accountIdListTemp This query returns result as concatenated string of comma separated values. If i want to get similar result in a single query how can i get it?
View Replies !
View Related
Conditional Where Clause With Comma Delimited String And Link Table
I have 3 tables:tblUsersuserID int PK(...)tblSportsSportID int PK(...)tblUsersAndSports (contains the link between users and sports..a single user may have multiple entries in this table)Usercode intSportID intNow I want a stored proc that enables visitors to search on all user that have a specific sportID.The SportIDs to search on are in the var @sports as a comma delimited string,like '3,6,7'@sports may also be null (or an empty string if that is more convenient for building the SQL) when a visitor does not want to search on any of the sports a user practices, in that case no selection based on the sport criteria should be done, so ONLY filter on sports when the value of @sports is not nullpseudo code:select * from tblUserswhere if @sports not null user.sports in @sportsand username=@usernameand age=@agehelp is greatly appreciated!
View Replies !
View Related
Function To Create Comma Separated List From Any Given Column/table.
Hi,I'm sure this is a common problem.. to create a single field from awhole column, where each row would be separated by a comma.I can do this for a specified table, and column.. and I've created afunction using VBA to achieve a more dynamic (and very slow) solution..so I would like to implement it using a user defined function in sql server.The problems I'm facing are, that I can't use dynamic sql in afunction.. and I also can't use temporary tables which could build up a'standard' table from parameters given to then perform the function on.So, with these limitations, what other options do I have?Cheers,Chris
View Replies !
View Related
Storing Comma Separated Values In A Single Column Of A Table
Hi, I have a table called geofence. It has a primary key geofence_id. Each geofence consists of a set of latitudes and latitudes. So I defined two columns latitude and longitude and their type is varchar. I want to store all latitude/longitude values as a comma separated values in latitude/longitude columns So in general how do people implement these types of requirements in relational databases? --Subba
View Replies !
View Related
How To Insert Results Of A SQL Query Into The Field Of Another Table, As Comma Seperated Values.
I am currently working on an application that requires, insertion of the results of a SQL Query in to the field of another table, in the form of a comma separated values. For example, to explain things in detail: create table dbo.phone_details (country varchar(20), state varchar(30), list_of_toll_free_numbers text) insert into dbo,phone_details values ( 'USA', 'CA', 'select Phone from phone_table where substring(phone, 1, 3) in ('800', '866', '877', '888')' ) The final output I desire is: country state list_of_toll_free_numbers ---------- ------- ----------------------------------------- USA CA 8009877654, 8665764398, 8776543219
View Replies !
View Related
SQL 2000 Table Field String Wrap
A string needs to be stored in a SQL 2000 table varchar(255) fileld in such a way that when emailed or printed out, it will display as below: Name: John DoePhone: 213-444-5555Email:jdoe@test.comCity: New YorkCountry: USA How can such a string be constituted?Thanks
View Replies !
View Related
Input String -&> Table -&> Output String?
I have a nasty situation in SQL Server 7.0. I have a table, in whichone column contains a string-delimited list of IDs pointing to anothertable, called "Ratings" (Ratings is small, containing less than tenvalues, but is subject to change.) For example:[ratingID/descr]1/Bronze2/Silver3/Gold4/PlatinumWhen I record rows in my table, they look something like this:[uniqueid/ratingIDs/etc...]1/2, 4/...2/null/...3/1, 2, 3/...My dilemma is that I can't efficiently read rows in my table, match thestring of ratingIDs with the values in the Ratings table, and returnthat in a reasonable fashion to my jsp. My current stored proceduredoes the following:1) Query my table with the specified criteria, returning ratingIDs as acolumn2) Split the tokens in ratingIDs into a table3) Join this small table with the Ratings table4) Use a CURSOR to iterate through the rows and append it to a string5) Return the string.My query then returns...1/"Silver, Platinum"2/""3/"Bronze, Silver, Gold"And is easy to output.This is super SLOW! Queries on ~100 rows that took <1 sec now take 12secs. Should I:a) Create a junction table to store the IDs initially (I didn't thinkthis would be necessary because the Ratings table has so few values)b) Create a stored procedure that does a "SELECT * FROM Ratings," putthe ratings in a hashtable/map, and match the values up in Java, sinceJava is better for string manipulation?c) Search for alternate SQL syntax, although I don't believe there isanything useful for this problem pre-SQL Server 2005.Thanks!Adam
View Replies !
View Related
Create A String Of Records From A Table In A Stored Procedure,
I have a table tblCustomers in a one-to-many relationship with tabletblProducts.What I want to do is to create a stored procudure that returns a listof each customer in tblCustomers but also creates a field showing astring (separated by commas)of each matching record in tblProducts.So the return would look like:CustID Customer ProductList1 Smith Apples, Oranges, Pears2 Jones Pencils, Pens, Paperetc...Instead of:CustID Customer Product1 Smith Apples1 Smith Oranges1 Smith Pears2 Jones Pencils2 Jones Pens2 Jones PaperWhich is what you get with this:SELECT tblCusomers.CustID, tblCusomers.Customer,tblProducts.ProductFROMtblCusomers INNER JOINtblProducts ONtblCustomers.CustID = tblProducts.CustIDI'd appreciate any help!lq
View Replies !
View Related
Query Result As Comma-separated List
Hi,I'n in an environment where I cannot make stored procedures. Now I needto make a query with a subquery in the SELECT part which gives a commaseparated list of results:SELECTp.id,listFunction(SELECT name FROM names WHERE name_parent=p.id) AS'nameList'FROM projects AS pThis query should return something like:1, "john,mike,petra"2, "bob,carl,sandra,peter,etclistFunction is (of course) not (yet) defined. Is this possible withoutthe use of stored procedures?Mike
View Replies !
View Related
Query To Get Values From Datetime Column Into Comma Separated Text
Hi All I am working on a query to get all the datetime values in a column in a table into a comma separated text. eg. ColumnDate --------------------------- 2005-11-09 00:00:00.0002005-11-13 00:00:00.0002005-11-14 00:00:00.0002005-11-16 00:00:00.000 I wanted to get something like 2005-11-09, 2005-11-13, 2005-11-14, 2005-11-16 Have just started SQL and hence am getting confused in what I think should be a relatively simple query. Any help will be much appreciated. Thanks
View Replies !
View Related
Update Query From Another Table On Datechanged Field
Hi all, sorry if this is the wrong place to put this.I have two tables, both contain address info. I would like to updateaddress1, address2, city, state, zipcode and country. May be a fewother fields.The table I am comparing to has many duplicates. The linkage betweenthe two table is by ssn. However I have one field that is date stampedas to which is the most current.How do I get the last date stamped record and update the other table?update group1 setaddress1 = c.address1address2 = c.address2city = c.citystate = c.statezipcode = c.zipcodecountry = c.countryfrom main c, group1 gwhere g.ssn = c.ssn and max(lastchanged)I know the above does not work but it is what I am try to do. Cananyone help?TIA!!!!
View Replies !
View Related
Coma Separated String Value As Function Parameter
Hi Let€™s say I have employees table that contains id column for the supervisor of the employee. I need to create a function that gets coma separated string value of the supervisors€™ ids, And return the ids of employees that the ENTIRE listed supervisors are there supervisor. (some thing like €œSelect id from employees where supervisor=val_1 and supervisor=val_2 and €¦ and supervisor=val_N) Is there a way to create this function without using sp_exec? I€™ve created a function that splits the coma separated value to INT table. (For use in a function that do something like: €œSelect id from employees where supervisor in (select val from dbo.SplitToInt(coma_separated_value)) ) Thanks , Z
View Replies !
View Related
Table Names In Stored Procedures As String Variables And Temporary Table Question
How do I use table names stored in variables in stored procedures? Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000 I receive the error 'must declare table variable '@tablename'' I've looked into table variables and they are not what I would require to accomplish what is needed. After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires. Code Snippet if exists(Select * from sysobjects where name = @temptablename) drop table @temptablename It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'. Heres what the stored procedure does. I duplicate a table that is going to be modified by using 'select into temptable' I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA' then I truncate the original table that is being modified and insert the temporary table into the original. Heres the actual SQL query that produces the temporary table error. Code Snippet Select * into #temptableabcd from TableA Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02) SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02', FROM TableB where ColumnB = 003860 Group By ColumnA, ColumnB TRUNCATE TABLE TableA Insert into TableA(ColumnA, ColumnB,Field_01, Field_02) Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02', From #temptableabcd Group by ColumnA, ColumnB The above coding produces Msg 208, Level 16, State 0, Line 1 Invalid object name '#temptableabcd'. Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible? Thanks for the help.
View Replies !
View Related
User Defined Function: Convert String Value Of Table To Table Object
Does anyone know where to find or how to write a quick user defined fucntionthat will return a table object when passed the string name of the tableobject. The reason why I want dynamicallly set the table name in a storedprocudue WITHOUT using concatination and exec a SQL String.HenceIf @small_int_parameter_previous = 1 then@vchar_tablename = "sales_previous"else@vchar_tablename = "sales"Endselect * from udf_TableLookup(@vchar_tablename )So if I pass 1, that means I want all records from "sales_previous"otherwise give me all records from "sales" (Sales_Previous would last yearssales data for example).udf_TableLookup would I guess lookup in sysobjects for the table name andreturn the table object? I don't know how to do this.I want to do this to avoid having 2 stored procedures..one for current andone for previous year.Please respond to group so others may benfiit from you knowledge.ThanksErik
View Replies !
View Related
SQL Query - Search Field For String
Hi, I have a SQL server 2005 database with a series of multiple fields. One of the fields has a array of strings seperated by semi-colons like so: Red;Green;Blue My question is, how can i run a query on all of the fields that have the value of say Green in it. Note that these values vary in different order and numbers. Thanks
View Replies !
View Related
Increment An ID Field In Sql Query String
Hello.. can anyone help me with this query string? String SQL = "INSERT Employee(Employee ID, UserName, JobRole, Department, Level, Email)(SELECT max(EmployeeID) + 1 FROM Employee) AS Employee ID, VALUES(EmployeeID, '" + newUserName + "', '" + newJobRole + "', '" + newDept + "', '" + newLevel + "', '" + newEmail + "')"; I am trying to insert values into a table, but i have an Employee ID field, which needs incrementing. How can i do this through my SQL query string? Is this possible? As it can't accept a NULL value. Thanks, Sandy
View Replies !
View Related
Parse Data From A String Field In A SQL Query
Ok,I have a field in a database that looks something like thisField1: BLAHBLAHBLAH (10.192.168.1)I need to either extract the IP address from this field or at leastformat it to only show the IP address in the result. The problem I amhaving with my limited experience is that the BLAHBLAH piece can varyin length and of course the IP piece can vary due to different IPaddress sizes. It seems to me there should be some kind of simplesolution since the IP address is enclosed but I cant "enclose" my mindaround what I need to do.
View Replies !
View Related
How To Manipulate String In Query And Create New Field
I'm very new to SQL server and can use some help. MyTable has ColumnA, which contains strings composed of 1 to 4 numeric characters (0 thru 9) followed by alphabetic characters. For example, "53ASDF". In my query, I need to create ColumnB, which takes the numeric prefix from ColumnA's string and prepends it with zeros, if necessary, to create a string of exactly 4 numeric characters. For example, I could get the following result: ColA ColB "6abc" "0006" "457def" "0457" "7232hij" "7232" I have implemented a temporary solution using a CASE statement: SELECT ColA, ColB = CASE WHEN ISNUMERIC(LEFT(ColA, 4)) = 1 THEN (LEFT(ColA, 4)) WHEN ISNUMERIC(LEFT(ColA, 3)) = 1 THEN '0' + (LEFT(ColA, 3)) WHEN ISNUMERIC(LEFT(ColA, 2)) = 1 THEN '00' + (LEFT(ColA, 2)) WHEN ISNUMERIC(LEFT(ColA, 1)) = 1 THEN '000' + (LEFT(ColA, 1)) ELSE '' END FROM MyTable Because of additional complexities, I need to implement the solution with a loop instead of a CASE statement. Can someone please describe such a solution? I'm very confused about how variables work in SQL Server, but made an attempt to implement a solution. Hopefully, someone can make corrections and describe how to use it with a SELECT statement. I would greatly appreciate any suggestions. This is what I started with: DECLARE @ColBstring char(4) DECLARE @num int SET @ColBstring = '' SET num = 1; -- Get the numeric prefix from ColumnA's string WHILE(isnumeric(substring(colA, 1, num)) = 1) @ColBstring = (substring(colA, 1, num) num = num + 1 -- Prepend the ColumnB string with zeros WHILE(LEN(@ColBstring) < 4) @ColBstring = '0' + @ColBstring Thanks for any help, Mike
View Replies !
View Related
How Do I Make A Autonumber Field In My Table!
Hiim new to ms sql server, having previously used mysql. How do i make a auto number field? What datatype shall i use for it? like autonumber for mysql. Ive tried setting my primary key field to uniqueidentifier data type but then i still need to manually add a guid key in there. i want it so it automatically generates a unique key everytime i add a new row. is this possible?!hope someone can help!thanks
View Replies !
View Related
Comma In Value Within Comma Separated File
I have a file which contains comma separated columns. One of columns contains names of companies. Sometimes the names of the companies have a comma as part of the name. For those, the value is surrounded by double-quotes. But it seems that SSIS ignores the double quotes and ONLY looks for the column separator. This causes my value to be split in half. Traditionally, I thought parsers that deal with this type of import do not automatically take the first comma following the double-quote as the column separator but instead look for the first comma following the ending quote. (i.e. Look at how Excel performs imports...) I cannot set the column separator of the column to double-quote comma since only those values that HAVE a comma in them are qualified. Any ideas? Here is sample fie content to see what I mean: 342123, Jason, 12345 21, Kim,4567 32.43, John Paul, 1245 23, "Mr. T", 98764 12, "Peter, Paul, Mary", 09643 The last entry should be imported as 12 in the first column, "Peter, Paul, Mary" in the second column and 09643 in the third but instead ends up as 12 in the first, "Peter in second column and Paul, Mary", 09643 in the last. (Oddly enough, if I remove the first column of numbers the import works like it is supposed.)
View Replies !
View Related
Naming A New Table From A Make Table Query
I want to use a Make Table Query to generate a new read-only Table. This I can do but my problem is that I want to be able to name the new Table containing only the records from a calendar year; e.y. Rents2001 and the next year another new table would be created and called Rents2002 and next year Rents2003 ............... I require the Table to be generated yearly. I know I could do this in other ways but I really require the Table as once I have it I will be doing other things with it to give the final report. Any suggestions how I can generate the Table with the YEAR being in the Table Name as part of running the Make Table Query? Thanks
View Replies !
View Related
Update Data To A Table From The Sum Of A Field From Another Table Based On Some Criteria
Hello Friends, I have two tables, And also I have Sample data in them. create table X (y int, m int, v int) insert into X select 2007,1,5 insert into X select 2007,1,3 insert into X select 2007,2,9 insert into X select 2007,2,1 select * from X Create table Y (fy int, fm int, v int) insert into Y select 2007,1,0 insert into Y select 2007,2,0 insert into Y select 2007,3,0 select * from X select * from Y I want to update the Table Y with the Sum of the Fields V from X based on the Criteria Y.fy = X.y and Y.fm = X.m Using temporary table cannot be done. Thanks in Advance, Babz
View Replies !
View Related
Query String Field Based On Text Format
I need to search a nvarchar field based on the format of the text. This field holds values in two formats: 000 000 000 000 and 000000. I only want to search through the records that are in the 000 000 000 000 format. Can anyone give me direction on how to go about doing this or give me some key words to search for on Google? Fixing this problem is not an option. This is a county tax DB from a poor county with almost a million records in it. Thanks for the help!
View Replies !
View Related
Remove Last Comma From The String
Hi All, I have one field with different values like F1 A,B,C, D,E G,H,I, My requirement is to remove the last comma from that string. I need the output should look like F1 A,B,C D,E G,H,I thanks, Mears
View Replies !
View Related
Query To Update Partial String
Sorry, I realize that this is probably a pretty simple question for you, but I am in a quick time bind and would really appreciate it if someone could help me with this update that I'm trying to run. Let's say you have a table (call it testtable) that contains the column "text". Now in column text you want to replace all instantiations of the phrase "in the US" with "to the United States", how would you structure your update query to perform this change (Please note that this phrase could be embedded in the middle of a larger sentence like 'Send a package in the US'. You'd want to change this instance to 'Send a package to the United States') Thanks in advance, I really appreciate it guys.
View Replies !
View Related
Help: About Ms Sql Query, How Can I Check If A Part String Exists In A String?
Hello to all, I have a problem with ms sql query. I hope that somebody can help me. i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)). Example Datas for Table Relationships: IDMember Relationships . 3387 (2345, 2388,4567,....) 4567 (8990, 7865, 3387...) i wirte a query to check if there is Relationship between two members. Query: Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567; select * from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in (select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM)) But I get nothing by this query. Can Someone tell me where is the problem? Thanks Best Regards Pinsha
View Replies !
View Related
Quotes Around The Comma Delimited String
I am trying to build a dynamic where statement for my sql stored prcoedure if len(@Office) > 0 select @strWhereClause = N'cvEh.chOfficeId in (select id from dbo.csfParseDeLimitedText( ' + @vchOffice + ',' + ''',''' + '))' + ' and ' + @strWhereClause In this case users can enter comma delimited string in their search criteria. So if they enter we1, we2, we3 then my sql statement should look like select @strWhereClause = cvEh.chOfficeId in (select id from dbo.csfParseDeLimitedText('we1', 'we2', 'we3'),',') My csfParseDeLimitedText function looks like this Create FUNCTION [dbo].[csfParseDeLimitedText] (@p_text varchar(4000), @p_Delimeter char(1))RETURNS @results TABLE (id varchar(100))ASBEGIN declare @i1 varchar(200)declare @i2 varchar(200)declare @tempResults Table (id varchar(100))while len(@p_text) > 0 and charindex(@p_Delimeter, @p_text) <> 0beginselect @i1 = left(@p_text, charindex(@p_Delimeter, @p_text) - 1)insert @tempResults select @i1select @p_text = right(@p_text, len(@p_text) - charindex(@p_Delimeter,@p_text))endinsert @tempResults select @p_text insert @resultsselect *from @tempResultsreturnEND My problem is it does not put quotes around the comma delimited stringso I want to put 'we1' , 'we2'. These single quotes are not coming in the dynamic sql statement. How can I modify my query so that single quotes around each entry should show up. Any help will be greatky appreciated. Thanks
View Replies !
View Related
Is This An Efficient Way To Create A Comma String
Hi there,I have created a sp and function that returns amongst other things acomma seperated string of values via a one to many relationship, thecode works perfectly but i am not sure how to test its performance.. Isthis an efficient way to achieve my solution.. If not any suggestionshow i can improve it.. What are the best ways to check query speed???MY SP:CREATE PROCEDURE sp_Jobs_GetJobsASBEGINSELECT j.Id, j.Inserted, Title, Reference, dbo.fn_GetJobLocations(j.id)AS location, salary, summary, logoFROM Jobs_Jobs j INNER JOIN Client c ON j.ClientID = c.idORDER BY j.Inserted DESCENDGO--------------------------------------------MY Function:CREATE FUNCTION fn_GetJobLocations (@JobID int)RETURNS varchar(5000) ASBEGINDECLARE @LocList varchar(5000)SELECT @LocList = COALESCE(@LocList + ', ','') + ll.location_nameFROM Jobs_Locations l inner join List_Locations ll onll.LocationID = l.LocationIDWHERE l.JobID = @JobIDRETURN @LocListENDAny help or guidance much appreciated...
View Replies !
View Related
Splitting A Comma Delimited String
I have a string like say: '3:4:5:4,2:4:1,4:1:2:5:2'. Now I need to split the substrings delimited by commas. So my final output shall be 3:4:5:4 2:4:1 4:1:2:5:2 I did write a piece of code to achieve the same, but I feel its not so efficient. Can anyone suggest me a better way, if any? My code is as follows: Declare @person as varchar(255), @cnt smallint,@loc smallint,@prevloc smallint, @str varchar(255) Select @prevloc=0,@loc=1,@cnt=1,@person = '3:4:5:4,2:4:1,4:1:2:5:2' While @loc != 0 begin set @prevloc=(case when @loc = 1 then 0 else @loc end) +1 set @loc = charindex(',',@person,@loc+1) Set @str = substring(@person,@prevloc,(Case when @loc = 0 then len(@person) - @prevloc + 1 else @loc - @prevloc end)) print 'String = ' + @Str set @cnt=@cnt+1 end RESULT ------ String = 3:4:5:4 String = 2:4:1 String = 4:1:2:5:2 Note: My actual purpose is to also sub split it again with ':' delimiter too. So looking for an efficient code.
View Replies !
View Related
SELECT WHERE IN Comma Delimited String
Hello I have a table with column Options where each field contains a comma delimited list of ID numbers. I want to select any records where a certain ID number appears in that list. So something like: SELECT * FROM Table t1 WHERE MyID IN (SELECT Options FROM Table t2 WHERE t1.ID = t2.ID) But that gives me the error: Syntax error converting the varchar value '39,20' to a column of data type int. I feel I'm close though! Could anyone point me in the right direction? Many thanks Square
View Replies !
View Related
UPDATE/INSERT To Make One-to-Many Table Become One-to-One
I have a scenario where two tables are in a One-to-Many relationshipand I need to move the data from the Many table to the One table sothat it becomes a One-to-One relationship.I need to salvage the records from the many table and without goinginto detail, one of the reasons I can't do the opposite asthere are records in the ONE table that I need to keep even if theydon't have any child records in the MANY table.Below I created the code to create the sample tables:1- tblProducts is the ONE side table2- tblProductDetails is the MANY side table3- tblProductsResult is the RESULT I expect to get after runningsome T-SQL code4- tblProductComponents is another MANY side table to tblProducts5- tblProductComponentsResult is the RESULT I expect to get...Some of the points to consider:6- Normally all UniqueID columns are to be IDENTITY. Forthis sample i am entering the UniqueID values myself.7- I don't want to create new tables like tblProductsResultand tblProductComponentsResult. I want to update the real tables.I have created the tblxxxResult tables only for this post.8- The goal is to update the name of the Product by giving it thename of the first matching Name from tblProductDetails.9- If there are more than one entry in tblProductDetails for eachProduct, then I need to create new Products inheriting the originalProduct's information including its child records from tblProductComponents.If you run the code and open the tables it will be much clearerto visually see what I want to achieve.CREATE DATABASE MyTestDBGOUSE MyTestDBGOCREATE TABLE [dbo].[tblProducts] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[Name] [varchar] (80) NULL,[TagNo] [int] NULL) ON [PRIMARY]GOINSERT INTO tblProducts VALUES (1, 'ABC', 55)INSERT INTO tblProducts VALUES (2, 'DEF', 66)INSERT INTO tblProducts VALUES (3, 'GHI', 77)INSERT INTO tblProducts VALUES (4, 'JKL', 88)CREATE TABLE [dbo].[tblProductDetails] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[Name] [varchar] (80) NULL,[ProductID] int) ON [PRIMARY]GOINSERT INTO tblProductDetails VALUES (1, 'ABC1', 1)INSERT INTO tblProductDetails VALUES (2, 'DEF', 2)INSERT INTO tblProductDetails VALUES (3, 'GHI', 3)INSERT INTO tblProductDetails VALUES (4, 'GHI2', 3)INSERT INTO tblProductDetails VALUES (5, 'GHI3', 3)INSERT INTO tblProductDetails VALUES (6, 'JKL2', 4)INSERT INTO tblProductDetails VALUES (7, 'JKL', 4)INSERT INTO tblProductDetails VALUES (8, 'JKL3', 4)INSERT INTO tblProductDetails VALUES (9, 'JKL4', 4)CREATE TABLE [dbo].[tblProductComponents] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[ProductID] int,[Component] [varchar] (80) NULL) ON [PRIMARY]GOINSERT INTO tblProductComponents VALUES (1, 1, 'ABCa')INSERT INTO tblProductComponents VALUES (2, 1, 'ABCb')INSERT INTO tblProductComponents VALUES (3, 1, 'ABCc')INSERT INTO tblProductComponents VALUES (4, 2, 'DEFa')INSERT INTO tblProductComponents VALUES (5, 2, 'DEFb')INSERT INTO tblProductComponents VALUES (6, 2, 'DEFc')INSERT INTO tblProductComponents VALUES (7, 2, 'DEFd')INSERT INTO tblProductComponents VALUES (8, 3, 'GHIa')INSERT INTO tblProductComponents VALUES (9, 4, 'JKLa')INSERT INTO tblProductComponents VALUES (10, 4, 'JKLb')CREATE TABLE [dbo].[tblProductComponentsResult] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[ProductID] int,[Component] [varchar] (80) NULL) ON [PRIMARY]GOINSERT INTO tblProductComponentsResult VALUES (1, 1, 'ABCa')INSERT INTO tblProductComponentsResult VALUES (2, 1, 'ABCb')INSERT INTO tblProductComponentsResult VALUES (3, 1, 'ABCc')INSERT INTO tblProductComponentsResult VALUES (4, 2, 'DEFa')INSERT INTO tblProductComponentsResult VALUES (5, 2, 'DEFb')INSERT INTO tblProductComponentsResult VALUES (6, 2, 'DEFc')INSERT INTO tblProductComponentsResult VALUES (7, 2, 'DEFd')INSERT INTO tblProductComponentsResult VALUES (8, 3, 'GHIa')INSERT INTO tblProductComponentsResult VALUES (9, 4, 'JKLa')INSERT INTO tblProductComponentsResult VALUES (10, 4, 'JKLb')INSERT INTO tblProductComponentsResult VALUES (11, 5, 'GHIa')INSERT INTO tblProductComponentsResult VALUES (12, 6, 'GHIa')INSERT INTO tblProductComponentsResult VALUES (13, 7, 'JKLa')INSERT INTO tblProductComponentsResult VALUES (14, 7, 'JKLb')INSERT INTO tblProductComponentsResult VALUES (15, 8, 'JKLa')INSERT INTO tblProductComponentsResult VALUES (16, 8, 'JKLb')INSERT INTO tblProductComponentsResult VALUES (17, 9, 'JKLa')INSERT INTO tblProductComponentsResult VALUES (18, 9, 'JKLb')CREATE TABLE [dbo].[tblProductsResult] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[Name] [varchar] (80) NULL,[TagNo] [int] NULL) ON [PRIMARY]GOINSERT INTO tblProductsResult VALUES (1, 'ABC1', 55)INSERT INTO tblProductsResult VALUES (2, 'DEF', 66)INSERT INTO tblProductsResult VALUES (3, 'GHI', 77)INSERT INTO tblProductsResult VALUES (4, 'JKL', 88)INSERT INTO tblProductsResult VALUES (5, 'GHI2', 77)INSERT INTO tblProductsResult VALUES (6, 'GHI3', 77)INSERT INTO tblProductsResult VALUES (7, 'JKL2', 88)INSERT INTO tblProductsResult VALUES (8, 'JKL3', 88)INSERT INTO tblProductsResult VALUES (9, 'JKL4', 88)I appreciate your assistance on this.Thank you very much
View Replies !
View Related
How To Parse A String Column With Comma Delimited
Hi,I would like to parse out each value that is seperatedby a comma in a field and use that value to join to another table.What would be the easiest way to do so without having towrite a function or routine ?EX.Table AAACOL1 COL21 11, 124, 1562 11, 505, 600, 700, ...Table BBBCOL1 COL211 Desc11124 Desc124156 Desc 156
View Replies !
View Related
Inserting Into ONE Column A Comma Delimted String??
I have a string of comma delimited values such as the following: 1,2,3,4,5 I have a table with ONE column only. How can I do an insert statement which will insert each of these values? If I type the following: "INSERT INTO tabletest SELECT 1,2,3,4,5" I get this error: "Insert error: column name or number of supplied values does not match table definition. " This makes sense because I do not have 5 columns I only have 1. But how can I get around this? Any help is most appreciated! Thanks in advance...mary
View Replies !
View Related
Stored Procedure Dbo.SalesByCategory Of Northwind Database: Enter The Query String - Query Attempt Failed. How To Do It Right?
Hi all, In the Programmability/Stored Procedure of Northwind Database in my SQL Server Management Studio Express (SSMSE), I have the following sql: USE [Northwind] GO /****** Object: StoredProcedure [dbo].[SalesByCategory] Script Date: 03/25/2008 08:31:09 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[SalesByCategory] @CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998' AS IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998' BEGIN SELECT @OrdYear = '1998' END SELECT ProductName, TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0) FROM [Order Details] OD, Orders O, Products P, Categories C WHERE OD.OrderID = O.OrderID AND OD.ProductID = P.ProductID AND P.CategoryID = C.CategoryID AND C.CategoryName = @CategoryName AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear GROUP BY ProductName ORDER BY ProductName //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code: Imports System.Collections.Generic Imports System.ComponentModel Imports System.Drawing Imports System.Text Imports System.Windows.Forms Imports System.Data Imports System.Data.SqlClient Imports System.Data.Common Imports System.Diagnostics Public Class ConnectionPoolingForm Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance Public Sub New() ' This call is required by the Windows Form Designer. InitializeComponent() ' Add any initialization after the InitializeComponent() call. 'Force app to be available for SqlClient perf counting Using cn As New SqlConnection() End Using InitializeMinSize() InitializePerfCounters() End Sub Sub InitializeMinSize() Me.MinimumSize = Me.Size End Sub Dim _SelectedConnection As DbConnection = Nothing Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged _SelectedConnection = DirectCast(lstConnections.SelectedItem, DbConnection) EnableOrDisableButtons(_SelectedConnection) End Sub Sub DisableAllButtons() btnAdd.Enabled = False btnOpen.Enabled = False btnQuery.Enabled = False btnClose.Enabled = False btnRemove.Enabled = False btnClearPool.Enabled = False btnClearAllPools.Enabled = False End Sub Sub EnableOrDisableButtons(ByVal cn As DbConnection) btnAdd.Enabled = True If cn Is Nothing Then btnOpen.Enabled = False btnQuery.Enabled = False btnClose.Enabled = False btnRemove.Enabled = False btnClearPool.Enabled = False Else Dim connectionState As ConnectionState = cn.State btnOpen.Enabled = (connectionState = connectionState.Closed) btnQuery.Enabled = (connectionState = connectionState.Open) btnClose.Enabled = btnQuery.Enabled btnRemove.Enabled = True If Not (TryCast(cn, SqlConnection) Is Nothing) Then btnClearPool.Enabled = True End If End If btnClearAllPools.Enabled = True End Sub Sub StartWaitUI() Me.Cursor = Cursors.WaitCursor DisableAllButtons() End Sub Sub EndWaitUI() Me.Cursor = Cursors.Default EnableOrDisableButtons(_SelectedConnection) End Sub Sub SetStatus(ByVal NewStatus As String) RefreshPerfCounters() Me.statusStrip.Items(0).Text = NewStatus End Sub Sub btnConnectionString_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnConnectionString.Click Dim strConn As String = txtConnectionString.Text Dim bldr As DbConnectionStringBuilder = _ProviderFactory.CreateConnectionStringBuilder() Try bldr.ConnectionString = strConn Catch ex As Exception MessageBox.Show(ex.Message, "Invalid connection string for " + bldr.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error) Return End Try Dim dlg As New ConnectionStringBuilderDialog() If dlg.EditConnectionString(_ProviderFactory, bldr) = System.Windows.Forms.DialogResult.OK Then txtConnectionString.Text = dlg.ConnectionString SetStatus("Ready") Else SetStatus("Operation cancelled") End If End Sub Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click Dim blnError As Boolean = False Dim strErrorMessage As String = "" Dim strErrorCaption As String = "Connection attempt failed" StartWaitUI() Try Dim cn As DbConnection = _ProviderFactory.CreateConnection() cn.ConnectionString = txtConnectionString.Text cn.Open() lstConnections.SelectedIndex = lstConnections.Items.Add(cn) Catch ex As Exception blnError = True strErrorMessage = ex.Message End Try EndWaitUI() If blnError Then SetStatus(strErrorCaption) MessageBox.Show(strErrorMessage, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error) Else SetStatus("Connection opened succesfully") End If End Sub Sub btnOpen_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnOpen.Click StartWaitUI() Try _SelectedConnection.Open() EnableOrDisableButtons(_SelectedConnection) SetStatus("Connection opened succesfully") EndWaitUI() Catch ex As Exception EndWaitUI() Dim strErrorCaption As String = "Connection attempt failed" SetStatus(strErrorCaption) MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error) End Try End Sub Sub btnQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click Dim queryDialog As New QueryDialog() If queryDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then Me.Cursor = Cursors.WaitCursor DisableAllButtons() Try Dim cmd As DbCommand = _SelectedConnection.CreateCommand() cmd.CommandText = queryDialog.txtQuery.Text Using rdr As DbDataReader = cmd.ExecuteReader() If rdr.HasRows Then Dim resultsForm As New QueryResultsForm() resultsForm.ShowResults(cmd.CommandText, rdr) SetStatus(String.Format("Query returned {0} row(s)", resultsForm.RowsReturned)) Else SetStatus(String.Format("Query affected {0} row(s)", rdr.RecordsAffected)) End If Me.Cursor = Cursors.Default EnableOrDisableButtons(_SelectedConnection) End Using Catch ex As Exception Me.Cursor = Cursors.Default EnableOrDisableButtons(_SelectedConnection) Dim strErrorCaption As String = "Query attempt failed" SetStatus(strErrorCaption) MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error) End Try Else SetStatus("Operation cancelled") End If End Sub /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// I executed the code successfully and I got a box which asked for "Enter the query string". I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out. Thanks in advance, Scott Chang
View Replies !
View Related
|
TRACKER
