SQL Comma Seperated List

Jul 26, 2005

Is there a way to return a comma seperated list in a  query?For example if I have this simple querySelect member_name From members It returns all the members in diff rows (lets assume we have 3)So the result will bemember_name--------------name1name2name3Instead of this I need to have the result in one row with values seperated by commas.member_name---------------name1,name2,name3I hope I am clear enough. Any help would be appreciated. Thank you.

View 4 Replies


ADVERTISEMENT

Report Designer: Need To List Fields From Multiple Result Rows As Comma Seperated List (like A JOIN On Parameters)

Apr 9, 2008



I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.

However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).

For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).

Something like:
List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac


How can I accomplish this?

I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.

Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?

View 21 Replies View Related

How Do I Create An In-line Comma Seperated List

Jul 20, 2005

Scenario:Table 1 (a id, b name)Table 2 (a FKid, d value)A standard join on a gives me something like:a1 b1 d1a1 b1 d2What I want is:a1 b1 d1,d2I can easily do this with a function or cursor, but is is somewhatslow, and I need to do this a lot and I don't really want to have tomaintain tons of functions or cursors.Thoughts?

View 3 Replies View Related

Comma Seperated Values

May 5, 2008

What is the best way? I have a field of comma seperated article type id values in a users profile. This list is a set of values that sows the article types the person does NOT want to see. Each article has an article type id. I need to do a select joining the article table to the member table that only shows those article id's that are not in the comma seperated list. How would I do that in a sql statement?

View 3 Replies View Related

Comma Seperated Select

May 14, 2008

Not sure the best way to do this. They are passing in a comma seperated string. I have a field that has comma seperated strings in it. I need to compare the 2 strings and do a select on that field that has any of the words in the string passed in. So if the pass...
"car,boat,van"

and in the field 2 of the 3 records get sent back
car,train,bike
boat,truck,plane
bike,plane,train

I would send them back the top 2 because the top one contains car and the second contains boat

View 5 Replies View Related

Storing Comma Seperated Values

Mar 7, 2008

Hi I have a text field called testing which shows the selection the user has made from a checked list. I want to save the contents of testing to a datadase with coloum varchar and with a comma to seperate the selections. It stores ok but sql queries do not recognise them as comma seperated values. e.g the selection from the vb code produses: Beauty, Fashion, Travel. Sql takes the lot as one. When I test a query say select from table type where Category = beauy it gives nothing. How do I store it so sql would recognise it as seperate enteries.
my vb code is below
Dim msg As StringDim li As ListItem
msg = ""For Each li In CaregoryCheckBoxList.Items
If li.Selected = True Then
'asp has a security default of no accetping html in input fields br is html code so causes error
'msg = msg & "<br>" & li.Text & " selected."
msg = msg & li.Text & "," & " "
End If
Next
Testing.Text = msg

View 10 Replies View Related

Script For Comma Seperated Values

Jul 23, 2005

To get rid of redundant data in a table, my cleint will be providingsomething like this:IDtokeep Ids to delete34 24,35,4912 14,178,145754 32,65,68I have to write a script for each of the above rows which looks likethis:-----------------------------------update sometableset id = 34where id in (24,35,49)delete from sometablewhere id in (24,35,49)-----------------------------------As I said I have to do this for EACH row. Can I somehow automate thisor will I need to write to same script for each row (there are about5000 rows in this audit table)Any help is highly appreciated.Here is the DDL and inserts for the audit table.IF object_id(N'dbo.dataclean','U') is not nullDROP TABLE [dbo].[dataclean]GOCREATE TABLE [dataclean] ([IdTokeep] int NULL ,[IdsTodelete] varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )GOINSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])VALUES(34,'24,35,49')INSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])VALUES(12,'14,178,1457')INSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])VALUES(54,'32,65,68')GO

View 3 Replies View Related

Passing Comma Seperated Parameter To SP

Jan 21, 2008

Hi,

My SP is,

ALTER PROCEDURE dbo.InventorySalesSummaryForReport(@ItemNumber nvarchar(30),@StoreId nvarchar(500),@Year int)

AS

DECLARE @SQL nvarchar(1000)

-- Searching ItemNumber in Inventory_SKU

DECLARE @Count int

DECLARE @ItemNumSKU varchar(50)

SET @SQL = N'SELECT @Count12 =COUNT(*) FROM Inventory WHERE Store_Id in (@StoreId1) AND ItemNum = @ItemNumber1'

EXECUTE sp_executesql @SQL,N'@Count12 int OUTPUT,@StoreId1 nvarchar(500),@ItemNumber1 nvarchar(30)',@Count12=@Count OUTPUT,@StoreId1 = @StoreId,@ItemNumber1 = @ItemNumber

IF (@Count = 0)

BEGIN

SET @SQL = N'SELECT @ItemNumSKU1 = ItemNum FROM Inventory_SKUS WHERE Store_Id in (@StoreId1) AND AltSKU = @ItemNumber1'

EXECUTE sp_executesql @SQL,N'@ItemNumSKU1 nvarchar(30) OUTPUT,@StoreId1 nvarchar(500),@ItemNumber1 nvarchar(30)',@StoreId1 = @StoreId,@ItemNumber1 = @ItemNumber,@ItemNumSKU1=@ItemNumSKU OUTPUT

SET @ItemNumber = @ItemNumSKU

END

-- Creating table variable to have values from 1 to 12

CREATE TABLE #MonthSales (MonthNumber int, MonthCost money,MonthPrice money,MonthQuan bigint)

DECLARE @Cnt INT

SET @Cnt = 1

WHILE(@Cnt <= 12)

BEGIN

INSERT INTO #MonthSales VALUES(@Cnt,0,0,0)

SET @Cnt = @Cnt + 1

END

--Joining query result with the table variable to get required result

DECLARE @Status1 Char(1)

SET @Status1 = 'C'

-- putting ' for comma seperated storeid

SET @SQL = N'SELECT'

SET @SQL = @SQL + N' MS.MonthNumber,'

SET @SQL = @SQL + N' ISNULL(Temp.MonthCost,0) MonthCost,'

SET @SQL = @SQL + N' ISNULL(Temp.MonthPrice,0) MonthPrice,'

SET @SQL = @SQL + N' ISNULL(Temp.MonthQuan,0) MonthQuan'

SET @SQL = @SQL + N' FROM'

SET @SQL = @SQL + N' (SELECT '

SET @SQL = @SQL + N' DATEPART(mm, DateTime) Month#'

SET @SQL = @SQL + N' ,SUM(CostPer*Quantity) As MonthCost'

SET @SQL = @SQL + N' ,SUM(PricePer*Quantity) AS MonthPrice'

SET @SQL = @SQL + N' ,SUM(Quantity) AS MonthQuan '

SET @SQL = @SQL + N' FROM '

SET @SQL = @SQL + N' Invoice_Totals '

SET @SQL = @SQL + N' INNER JOIN Invoice_Itemized ON Invoice_Totals.Invoice_Number = Invoice_Itemized.Invoice_Number '

SET @SQL = @SQL + N' WHERE '

SET @SQL = @SQL + N' Status = @Status AND Invoice_Totals.Store_ID in (@StoreId1) '

SET @SQL = @SQL + N' AND ItemNum = @ItemNumber1'

SET @SQL = @SQL + N' AND DATEPART(yy,datetime)=@Year1'

SET @SQL = @SQL + N' GROUP BY'

SET @SQL = @SQL + N' DATEPART(mm, DateTime)'

SET @SQL = @SQL + N' ) Temp '

SET @SQL = @SQL + N' RIGHT OUTER JOIN #MonthSales MS ON MS.MonthNumber = Temp.Month#'

SET @SQL = @SQL + N' ORDER BY MS.MonthNumber'

EXECUTE sp_executesql @SQL,N'@Status char(1),@StoreId1 nvarchar(500),@ItemNumber1 nvarchar(30),@Year1 int',@Status = @Status1,@StoreId1 = @StoreId,@ItemNumber1 = @ItemNumber,@Year1=@Year


It is working If I am passing sigle storeId to the above SP. If I am selecting multiple StoreIDs from the report. It is not returning correct value. Can you please give me a suggestion?

Thanks

View 3 Replies View Related

Select Many Records Into 1 Comma Seperated Field.

Oct 30, 2006

I want to pull out some information for documenting my user accounts.I want to be able to show all of the users and what companies those users have access to.

The table which stores the company information lists it in multiple rows, so there will be many results for each user
user -- companyid
johnd -- 2
johnd -- 5
johnd -- 1

I want to select this into one record so it will show up like this
user -- companies
johnd -- 2,5,1

The only way I know how to do this would be with a sub-query that uses a cursor to loop through & dump it into one string value, and then output the string value to the field. This seems extremely inefficient for such a seemingly simple task.

Can somebody help me out here?
thanks

View 3 Replies View Related

Comma Seperated Field To Arrays For Each Record?

May 12, 2008

Hello,

My SQL knowledge is limited so if I get stuff wrong then correct me... but I can imagine this task will be quite testing...

I am working on a system that logs ([Audit] table) the changes to fields on some tables using a Trigger on UPDATE. I need to produce a 'quick' report that returns the date when the tables overallStatus field was set to 1.

In the [Audit] table I can find all the field changes for the record in question using this SQL...

select *
from audit
where rowid = 1309606
order by auditID asc


My problem is filtering this data. The fields I need are formatted as below, see records returned, in ASC order...

audit.AuditID = 2652583
audit.OperationTime = 2008-04-24 15:12:07.740
audit.ColumnDetail = 'estimatedProductionPriceactualProductionCost'
audit.EditDetail = '0.00000.0000'


audit.AuditID = 2658460
audit.OperationTime = 2008-04-25 10:51:47.930
audit.ColumnDetail = 'overallStatusInsertionStatus'
audit.EditDetail = '05'



audit.AuditID = 2665723
audit.OperationTime = 2008-04-25 22:06:50.200
audit.ColumnDetail = 'overallStatusdespatchDateInsertionStatus'
audit.EditDetail = '1Β 3'



audit.AuditID = 2711092
audit.OperationTime = 2008-04-30 17:22:12.593
audit.ColumnDetail = 'overallStatusInsertionStatus'
audit.EditDetail = '34'



audit.AuditID = 2713217
audit.OperationTime = 2008-04-30 20:46:34.817
audit.ColumnDetail = 'clientOrderNumber'
audit.EditDetail = 'PAT12P7640'


The funny character, , is ASCII 127.


So need to find when 1309606's overallStatus was changed to 1. Manually looking at the data, I can see overallStatus was modified in 3 of the above 5 [Audit] records. It started life as 0, then went to 1 and then to 3. I'm aware that I need to look at the previous Audits date for when it was changed to the value I'm looking for... So it was changed to 1 on 2008-04-25 10:51:47.930.


What is the best way to approach this problem? I'm hoping to use T-SQL only and not have to use an external scripting language, unless I can embed vbscript or jscript inside a T-SQL function and then use arrays, etc?

Somehow I need to convert the ASCII(127) seperated list of fieldname into an array or list, find the index of the fieldname 'overallStatus' and then lookup that value in the datafield.


Hope that makes sense and any help would be great!


Cheers,
Nick

View 6 Replies View Related

Field Contains Multiple Values Seperated By Comma

May 3, 2006

Hi All

I'm really new to SQL and would appreciate a solution to the following problem.

I have a table that contains 1 record. in that record there is a column called CODES which contains multiple values that are seperated by a comma.

C200,C201,C202,C204,C200,C203,C200,C202,C203,C205,C200,C202,C203,C204,C205,C206,C200,C201,C202

I need a solution to select all values from CODES that are distinct so I end up with the following.

C200,C201,C202,C203,C204,C205,C206

Any help would be really appreciated. Please bear in mind I'm a complet novice.

View 4 Replies View Related

Extracting Multiple Values As Comma Seperated

Jul 17, 2007

Hi all,



This is my table :

WorkstationNo UserID

101 a1

102 a2

103 a3

101 a2



and there are many other fields too. The output I need is something like this



WorsktationNo OccupiedBy

101 a1,a2

102 a2

103 a3



In the similar fashion I would also require to retrieve the values based on the UserID something like this

UserID Workstations

a1 101

a2 101,102

a3 103



Could someone tell me how to write the query for this.



View 9 Replies View Related

Need Sql To Return The Result Of A Query As Comma Seperated Values.

Jun 28, 2005

Hi,   I need a sql that returns the query result as comma seperated list of values, instead of several rows. Below is the scenario...   Table Name - Customer   Columns - CustomerID, Join DateSay below is the data of Customer table ...CustomerID    JoinDate1                   04/01/20052                   01/03/20033                   06/02/20044                   01/05/20025                   09/07/2005Now i want to retrieve all the customerid's who have joined this year. Below is the query that i use for this case.Select CustomerID from Customer where JoinDate between '01/01/2005' and GetDate()This gives the below result as two rows.CustomerID15But i need to get the result as '1,5' (comma seperated list of resulting values).Any help is highly appreciatedThanks in AdvanceRamesh

View 4 Replies View Related

Inserting From A Comma-seperated Ascii-file With Logics

Nov 17, 1999

Hi all !

I want to insert things to SQL-server from an ascii file but I also want to add logics with IF - Then _ Else statements. I guess the only way is to make a
stored Procedure.

How do you do when you want to read from a text file using the data into variables and then write it into the database ?

View 2 Replies View Related

Splitting A Comma Seperated Column Into Multiple Rows

Jan 18, 2006

Hi i want to create a table as follows :if exists (select * from dbo.sysobjects where id =object_id(N'[Indexes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [Indexes]GOCreate table Indexes(indexname Varchar(100), index_DescriptionVarchar(100), index_keys Varchar(100))GOINSERT INTO Indexes EXEC sp_helpindex 'SDM_Fact_Order_Detail'GOThis will give me a table (northwind)IX_Auto_SDM_Fact_FK_Shipped_Date nonclustered located onSAMIS_SDM_Index FK_Shipped_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Dateclustered located onSAMIS_SDM_Data1FK_Insert_Date, FK_Insert_TimeAs you see sp_helpindex will give me a comma seperated field. I wantto split the third column FK_Insert_Date, FK_Insert_Time into a extrarowLike this :IX_Auto_SDM_Fact_FK_Shipped_Date FK_Shipped_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Date FK_Insert_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Date FK_Insert_TimeCan anyone help me with this?ThanxHennie

View 1 Replies View Related

Get All The Matching Values In ColB And Display It Against ColA As Comma Seperated In The Same Row

Nov 6, 2007



Hi all sql gurus,

I have a table that has product name and submissionID and some other columns. I am really concerned about these two columns. My task is to get all the submissionIDs for a particular product name and display SubmissionIDs seperated by commas against each product name .
The tables below might give a better idea
current scenario:
Product Name SubmissionID columnC Column D
AAA 123
AAA 456
BBB 111
ccc 121
AAA 789
Expected result:
Product Name SubmissionID columnC Column D
AAA 123,456,789
BBB 111
CCC 121
Any suggestions/pointers would be highly appreciated.

View 5 Replies View Related

How To Insert Results Of A SQL Query Into The Field Of Another Table, As Comma Seperated Values.

Mar 7, 2008



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 8 Replies View Related

SQL 2012 :: List All Different Values That Go With Single CAS ID To Appear As Comma Separate List

Jun 15, 2015

So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function

So the output would look like :

CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]

SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0

GROUP BY
preop.Case_ID

View 8 Replies View Related

SELECT WHERE (any Value In Comma Delimited List) IN (comma Delimited List)

Jul 2, 2005

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

View 3 Replies View Related

Comma Delimited List Of Views

Aug 13, 2007

Is it possible to get a comma delimited list of the views in a DB?

View 7 Replies View Related

UDF To Split A Comma Separated List

Feb 26, 2008

Hello. I need to write a UDF that would split a comma separated list and return 4 values. I need to return the first 4 values and ignore the commas after that. If there are no commas in the string that's passed then just return the table with empty strings. The UDF should accept 2 inputs. The ntext and a position and return a value based on the position.For example: 1,2,3,textshould createPosition | Value-------------------------1|12|23|34|textand return a value based on the position.  If there are more than 3 commas for example1,2,3,This string, though short, contains a commashould createPosition | Value-------------------------1|12|23|34|This string, though short, contains a commaand return a value based on the position. And if there are are less than 3 commas in the string passedFor example: 1,2 or NULL or 2:3.5 or This is a string with no commasshould createPosition | Value
-------------------------
1| (empty string)
2| (empty string)
3| (empty string)
4| (empty string)and return a value based on the position.This is what I wrote so far. CREATE  function GetValueFromPosition  (@Input nvarchar(4000), @position int)Returns nvarchar(4000)AsBegin    -- Declare the return Variable    Declare @ReturnValue nvarchar(4000)        Select @ReturnValue = LTRIM(RTRIM(member_id)) From dbo.SplitString(@Input, ',') Where position = @position     Return @ReturnValueEnd CREATE Function SplitString(@text varchar(8000), @delimiter varchar(1) = ',')-- This function splits a string of CSV values and creates a table variable with the values.-- Returns the table variable that it createsRETURNS @Strings TABLE(    position int IDENTITY PRIMARY KEY,    member_id varchar(8000))ASBEGIN    Declare @index int        Set @index = -1             WHILE (LEN(@text) > 0)           BEGIN        SET @index = CHARINDEX(@delimiter , @text)         IF (@index = 0) AND (LEN(@text) > 0)                BEGIN                 INSERT INTO @Strings VALUES (@text)            BREAK           END             IF (@index > 1)                 BEGIN              INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))              SET @text = RIGHT(@text, (LEN(@text) - @index))            END            ELSE                SET @text = RIGHT(@text, (LEN(@text) - @index))           END        RETURNEND I am trying to modify these according to what I need but its not working. Please help. Thank you.   

View 1 Replies View Related

How Can I Get A Comma Delimited List Of The Views In My Db

Mar 18, 2008

How can I get a comma delimited list of the views in my db?
("view1","view2","view3",etc...)

View 3 Replies View Related

Creating A Comma Delimited List

Jul 11, 2005

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.

View 3 Replies View Related

Inner Join On A Comma Delimited List?

Jul 12, 2006

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?

View 1 Replies View Related

Matching Comma Separated List

Jun 7, 2008

I have the following SQL Query:

SELECT user_profiles.userFirstName, user_profiles.userInitial, user_profiles.userLastName, user_types.userTypeDesc, user_profiles.userOfficeIDs, user_profiles.userEmail
FROM user_profiles
INNER JOIN user_types ON user_types.userTypeID = user_types.userTypeID

The field userOfficeID contains a comma separated list of values such as "1,2" to identify that the user is in both the NJ and NY office.


Table office_locations
OfficeID officeState
1 NJ
2 NY
3 CT

I would like the output to be something similar to:

Name Office userOfficeIDs value
John Smith NJ 1
Mary Smith NJ/NY 1,2
Jim Smith NJ/CT 1,3
Mike Smith NY 2

Any direction would be appreciated.

Thanks

View 19 Replies View Related

Select List Split By Comma

Oct 29, 2014

i have a list of data as below:

Company Email
A AAA@abc.com
A BBB@abc.com
B xxx@def.com

i would like to write a query to list out as below where select the company A
AAA@abc.com, BBB@abc.com, ...

View 2 Replies View Related

How To Use Comma Separated Value List In The Where Clause?

Jun 28, 2006

How to use comma separated value list in the where clause?

I would like to do something like the following (Set voted = true for all rows in tblVoters where EmpID is in the comma separated value list).

update tbl_Voters
set voted = true
where EmpID in @empIdsCsv

Where, @empIdsCsv = €™12,23,345,€™ (IDs of the employees)

Since the above is not possible I have done the following dynamic query:

-- Convert the comma separated values to conditional statement like EmpID = {id} or EmpliD = {id}€¦
set @empIdsCsv = 'EmpID=' + substring(@empIdsCsv , 0, len(@empIdsCsv )) -- Remove trailing comma
set @empIdsCsv = replace(@empIdsCsv , ',', ' or EmpID=')

declare @markVoters varchar(8000)
set @markVoters = '
update tbl_Voters
set voted = true
where €™ + @empIdsCsv

--Execute the dinamic query
exec (@markVoters)

The above code generates the following dynamic query:
update tbl_Voters
set voted = true
where
EmpID= 12 or EmpID=23 or EmpID=345

The obvious drawback here is the performance and the limitation of the dynamic query length (8000 chars).

Can someone suggest a better solution with the ability to use comma seperated values in the where clause?

View 3 Replies View Related

Help With Subquery: Comma-delimited Id List To Value

Aug 17, 2007

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

View 4 Replies View Related

Comma Delimited List Of IDs To A Recordset From 2 Tables...

Jan 20, 2006

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!

View 5 Replies View Related

Comma Delimited Values List In Where Command

Jan 14, 2014

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?

View 3 Replies View Related

Query Result As Comma-separated List

Jul 23, 2005

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 12 Replies View Related

Transact SQL :: Replace With A Comma Separated List

Aug 5, 2015

I have a replace statement like the following:

select Β (replace('FMG','FMG','FM'))

So this is straightforward as it will replace the word 'FMG' with 'FM'

However, SSRS is passing a comma separated list like this:

select Β (replace('FMG','AFM','FMG','FM'))

And what I need to do is replace the comma separated list of 'FMG','AFM' only. Β I tried this:

select Β (replace('''FMG','AFM''','FMG','FM'))

But it still complaining about syntax errors. How do I get the comma separated list to be seen by the replace function?

View 7 Replies View Related

Pass Comma Delimited List To Stored Procedure

May 2, 2008

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

View 8 Replies View Related







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