Concatenated String Of Comma Separated Values (was Help With Query)

Nov 21, 2006

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 4 Replies


ADVERTISEMENT

Transact SQL :: Convert Comma Separated String Values Into Integer Values

Jul 28, 2015

I have a string variable

string str1="1,2,3,4,5";

I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :

declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)

But this query throws following error message:

Conversion failed when converting the varchar value '3,4,6,7' to data type int.

View 4 Replies View Related

Query To Get Values From Datetime Column Into Comma Separated Text

Dec 20, 2005

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

DB Engine :: How To Pass Values With Comma To Comma Separated Param In SP

Apr 27, 2015

I have one sp which has param name as cordinatorname varchar(max)

In where condition of my sp i passed as

coordinator=(coordinatorname in (select ltrim(rtrim(value)) from dbo.fnSPLIT(@coordinatorname,',')))

But now my promblm is for @coordinatorname i have values as 'coorcinator1', 'coordinato2,inc'

So when my ssrs report taking these values as multiselect, comma seperated coordinator2,inc also has comma already.

View 4 Replies View Related

Procedure Or Query To Make A Comma-separated String From One Table And Update Another Table's Field With This String.

Feb 13, 2006

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

Data Warehousing :: Query To Extract All Text After Hyphen From String Containing Comma Separated Text

Aug 27, 2015

I have a parameter value as shown below and this is dynamic and can grow 

Example : 101-NY, 102-CA, 165-GA
116-NY, 258-NJ, 254-PA, 245-DC, 298-AL
How do I get the values in the below format
NY,CA,GA   --- each state to be followed with comma and the next state
NY,NJ,PA,DC,AL   --- each state to be followed with comma and the next state

correct query that will fetch  only state names and not the numbers.

View 8 Replies View Related

Reporting Services :: SSRS - Get Common Values Between Two Columns Where Values Sorted Comma Separated

May 6, 2015

I have a situation in SSRS to get the common values between the two columns where the values are sorted comma separated as below.Ex:

ColumnA :  abc,cde,efg    
ColumnB : cde,xyz,abc    

the result in    

ColumnC : cde,abc

similarly Column A and B will have n number records. I need to right an expression or the Code function to get the required result in ColumnC. I am using SharePoint Lists as Datasource. Cannot write SQL query to achieve this requirement.

View 5 Replies View Related

Reporting Services :: Selecting Multiple Parameters Values For Comma Separated Values In SSRS?

Jun 17, 2012

I am SSRS user, We have a .net UI from where we want to pass multi select values, but these values are comma separated in the database. how can I write a sql query such that when I select multi values on my UI, the comma separated values are take care of.

View 5 Replies View Related

Comma Separated String To Int

Jan 27, 2008

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

Get Values Separated By Comma

Nov 30, 2004

Hello, I need your advice.
Here's my scenario.

Table A
------------
id name
100 apple
115 grape
125 tomato
145 melon


Table B
-------------
id Fruits
11 100, 115, 145
12 125, 115
13 100


I thought i could get the list of fruits using this statement:

select name
from A where id IN (select fruits from B where id = 11)

But apparently not, it's working if
select name
from A where id IN (select fruits from B where id = 13)

That means it does not recognize values seperated by comma. Anyone who has any idea how to make it work?

Thanks in advance.

HS.

View 5 Replies View Related

Sql Statement Comma Separated String

May 3, 2005

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

Column Values Into Comma Separated Row?

Jul 6, 2015

I have some column values:-

employee_salary | dept

30000 1
35000 1
40000 1

I need employee-salary in one row separated by comma by executing a sql query i.e

dept1_salary

30000, 40000, 50000

View 1 Replies View Related

Logic Behind Comma Separated Values

Jun 3, 2014

I just learned to use following query to convert columns to rows separated by comma.

Here is that :

Declare @list varchar(max)
select @list = isnull(@field+ ',','') + columname from table1
select @list

This produces the output I want but I'm confused with the statement. I just learnt it by heart. I don't know the meaning of it particular for the statement "select @list = isnull.............. from table1" . What exactly it does to give the desired output?

View 2 Replies View Related

Using A Comma-separated String Using Stored Procedure And IN

May 27, 2005

Hello,

I was wondering if it's possible to pass in a comma separated string
"12,14,16,18" and use it in a stored procedure with "IN" like this:

@SubRegions varchar(255) <-- my comma separated string

        SELECT *
        FROM myTable
        WHERE tbl_myTable.SubRegionID IN (@SubRegions)

It tells me it has trouble converting "'12,14,16,18'" to an INT. :(

View 2 Replies View Related

Select Statement For Comma Separated Values

Feb 9, 2006

hi,
my sample SQL Server DB Tables are like,
SID Skill
--- -------
1 JAVA
2 ORACLE
3 C
4 C++

PID Skillset
--- ---------
1 1,2,3
2 2,4
3 1,2,3,4
4 3
I need the Query to display Person skills as follows...
PID Skillset
--- --------------
1 Java,Oracle,C
2 Oracle,C++
3 Java,Oracle,C,C++
4 C

and another query for Search..
if i give the search string as Java,C or i will pass the SID 1,3. i need to diplay the person records which contains the SID.

output will be...
PID Skillset
--- --------------
1 Java,Oracle,C
3 Java,Oracle,C,C++
4 C

or

PID Skillset
--- ---------
1 1,2,3
3 1,2,3,4
4 3
Plz help meee..
Thanking you in advance for your help.

View 1 Replies View Related

Count Only Specific Comma Separated Values

Jan 5, 2015

I'd like to limit my query results to only items that match any part of a dynamic csv string table but am having some trouble (postgres SQL). Details: I need to calculate how many hours our staff spends seeing clients. Each staff has different appointments that can count toward this. The specified appointments for each staff are listed as comma separated values. My existing query calculates the appointment hours for each staff in a given time period.

However, I need limiting my query to only include specified activities for each staff. My current where clause uses IN to compare the appointment (i.e. activity) listed in the staff's schedule with what is listed an an approved appointment type (i.e. performance target activity). The query runs but it seems to only count one of the activities listed in the csv rather then count all the activities that match with the csv.

select (sum (kept)/60) from (select distinct rpt_scheduled_activities.staff_id as sid,
rpt_scheduled_activities.service_date, rpt_scheduled_activities.client_id,
from rpt_scheduled_activities inner join rpt_staff_performance_target on rpt_scheduled_activities.staff_id = rpt_staff_performance_target.staff_id where

[code]...

View 1 Replies View Related

Obtaining Column Values Separated By Comma

Sep 26, 2006

How do I get the values of a column from a table separated by a comma.

For example

Suppose I have a table with column Levels (below), I want the values of the corresponding column separated by a comma, so that I can use this in a different query to pull these values from a different table

Levels
Level1Name
Level1Value
Level2Name
Level2Value

Result should look like
Level1Name, Level1Value, Level2Name, Level2Value


Thanks
Suresh

View 4 Replies View Related

Comma Separated Values In A Column Of A Table

Jul 13, 2007

Hi,
I want a column in a database table to store comma separated values.
So can I store it as a string type(varchar,nchar) using commas?
What are the other alternatives provided in Sql Server 2005

--Subba Rao

View 10 Replies View Related

Comma Separated Values To Stored Procedures

Jul 4, 2006

Hi All,i hv created a sp asCreate proc P @iClientid varchar (100)asBeginselect * from clients where CONVERT(VACHAR(100),iClientid) in(@iclientid)endwhere iclientid = int data type in the clients table.now if i pass @iclientid as @iclientid = '49,12,112'but this statement throws an conversion error ( int to char error).is there any way to fetch records from a select statement using astring???Thanks in Advance.

View 3 Replies View Related

Split Comma Separated Values Into Columns

Feb 16, 2008



Hi,
I have data like this in my table:

AppId Gender
1 x
2 y

3 x, y
4 x, y, z

I need to transform like this:
AppID Gender
1 x
2 y
3 x
3 y
4 x
4 y
4 z

How to do this?

Thanks in advance

View 10 Replies View Related

Normalizing Comma Separated String To Multiple Records

Oct 17, 2012

I need to normalise comma separated strings of tags (SQL Server 2008 R2).

E.g. (1, 'abc, DEF, xyzrpt') should become
(1, 'abc')
(1, 'DEF')
(1, 'xyzrpt')

I have written a procedure in T-SQL that can handle this. But it is slow and it would be better if the solution was available as a view, even a slow view would be better.

Most solutions I found go the way round: from (1, 'abc'), (1, 'DEF') and (1, 'xyzrpt'), generate (1, 'abc, DEF, xyzrpt').

If memory serves, it used "FOR XML PATH". But it's been a while and I may be totally wrong.

View 2 Replies View Related

Select Comma Separated Values From Single Column

May 27, 2008

Hi,

I have a table -- Table1.
It has two columns -- Name and Alpha.
Alpha has comma separated values like -- (A,B,C,D,E,F), (E,F), (D,E,F), (F), (A,B,C).

I need to pick the values of column -- Name , where in values of Alpha is less than or equal to 'D'.

I tried <=, but got only values less than 'D', but was not able to get equal to 'D'.

Any suggestions??

View 4 Replies View Related

How To Split Comma Separated Values And Show As Rows

Apr 19, 2013

I have an requirement where i need to show Employee Table and CustomerMeta Table joins In CustomerMeta Table (CustID)

Reference to Employee Table and Metavalues table Metavalues table is like master table.

In Application i will get multiple select box selection (DrivingLicense,Passport etc;) so that data will be inserted in comma(',') separated values So in my desired output i need to show as i need to show split those comma separated and for every MetaTypeID MetaTypeName as a row as showed in desired output

Metavalues table :

MetaID Metavaluedescription
1 Driving License
2 Passport
3 AadharCard
4 EducationalProof
5 ResidentialProof

CustomerMeta Table :

CustID MetaTypeID MetaTypeName
2 1,2,3,4,5 DrivingLicense,Passport,AadharCard,EducationalProof,ResidentialProof
3 1,2,3DrivingLicense,Passport,AadharCard

Employee Table

EmpID CustID EmPname
1001 2Mohan
1002 3 ramu

Desired OutPut :

EMPID CustID EmPname MetaTypeID MetaTypeName
1001 2 Mohan 1 Driving License
1001 2 Mohan 2 Passport
1001 2 Mohan 3 AadharCard
1001 2 Mohan 4 EducationalProof
1001 2 Mohan 5 ResidentialProof
1002 3 ramu 1 Driving License
1002 3 ramu 2 Passport
1002 3 ramu 3 AadharCard

View 20 Replies View Related

Transact SQL :: Using A Variable Containing Comma Separated Values In IN Clause?

Jun 12, 2015

I am try to use a variable containing comma separated values in the IN clause.

View 10 Replies View Related

Transact SQL :: Get 3 Comma Separated Values Into 3 Column Of A Table

Aug 21, 2015

I have 3 variables that gets comma separated values. My requirement is to get them into a temporary table with 3 columns and each column should have single value. E.g. if 

Declare @SID varchar(max),@CID VARCHAR(MAX),@KID VARCHAR(MAX)
Set @SID='1,2,3,4'
Set @CID='6,7,8,9'
Set @KID='A,BB,CCC,DDDD'

--Now my requirement is to get them in a temp table with 3 column and different rows as per comma separated values in variables.

Now my requirement is to get them in a temp table with 3 columns and different rows (as per number of comma separated values in variables) E.g.

16A
27BB
38CCC
49DDDD

How i can use them for joining with other tables.

View 5 Replies View Related

SQL Server 2008 :: Split Comma Separated String Into Columns?

Apr 24, 2015

Our front end saves all IP addresses used by a customer as a comma separated string, we need to analyse these to check for blocked IPs which are all stored in another table.

A LIKE statement comparing each string with the 100 or so excluded IPs will be very expensive so I'm thinking it would be less so to split out the comma separated values into tables.

The problem we have is that we never know how many IPs could be stored against a customer, so I'm guessing a function would be the way forward but this is the point I get stuck.

I can remove the 1st IP address into a new column and produce the new list ready for the next removal, also as part of this we would need to create new columns on the fly depending on how many IPs are in the column.

This needs to be repeated for each row

SELECT IP_List
, LEFT(IP_List, CHARINDEX(',', IP_List) - 1) AS IP_1
, REPLACE(IP_List, LEFT(IP_List, CHARINDEX(',', IP_List) +0), '') AS NewIPList1
FROM IpExclusionTest

Results:

IP_List
109.224.216.4,146.90.13.69,146.90.85.79,46.208.122.50,80.189.100.119
IP_1
109.224.216.4
NewIPList1
146.90.13.69,146.90.85.79,46.208.122.50,80.189.100.119

View 8 Replies View Related

SQL Server 2012 :: Comma Separated List Of Distinct Values

Oct 14, 2015

I am trying to create a comma delimited list of InvNo along with the JobNo .

CREATE TABLE #ListString
(
JobNo VARCHAR(10),
InvNo VARCHAR(MAX)
)
INSERT INTO #ListString ( JobNo, InvNo )
SELECT '3079', 'abc'

[Code] ....

View 6 Replies View Related

Storing Comma Separated Values In A Single Column Of A Table

Jul 13, 2007

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

Show Multiple Values In Single Textbox Comma Separated

Jan 2, 2008



I have a field called "Owners", and it's a child to an "Activities" table.

An Activity can have on or more owners, and what I'd like to do is some how comma separate the values that come back if there are more than one owners.

I've tried a subreport, but because the row is colored and if another field, title, expands to a second row (b/c of the length) and the subreport has just one name, then the sub-report has some different color underneath due to it being smaller in height.

I'm kinda stuck on how to do this.

Thanks!

View 3 Replies View Related

How Do You Parse A Single Field List Of Values Separated By Comma?

Jan 3, 2008


IE:
ID ContactID

1 4, 5, 6, 8
2 3,4,6

Someone coded their database like this. It is a SQL server table with these two fields.
How do I use SSIS to parse out that single field?

View 5 Replies View Related

SQL Server 2012 :: Combining Values Into Comma Separated List - Serialize

Apr 18, 2014

I have a requirement for SSRS where the input has the following structure:

Store NumberStore Owner
542 Jaklin Givargidze
542 Raymond G. Givargidze
557 Hui Juan Lu
557 Tong Yu Lu

but the user would like to see the following:

Store Number

View 1 Replies View Related

SQL Server 2012 :: Combining Values Into Comma Separated List - Serialize?

Apr 18, 2014

I have a requirement for SSRS report where part of the input has the following structure:

Store NumberStore Owner
542 Jaklin Givargidze
542 Raymond G. Givargidze
557 Hui Juan Lu
557 Tong Yu Lu

but the user would like to see the following:

Store Number Store Owner
542 Jaklin Givargidze, Raymond G. Givargidze
557 Hui Juan Lu, Tong Yu Lu

I am sure that this can be coded, just don't know how. I believe that proper term is to "serialize" the values.

View 2 Replies View Related

SQL Server 2008 :: Turn Field Values Into Comma Separated List

Aug 11, 2015

I have about 100 K records of the form below in Example 1 and I would like to turn them into the form of Example 2, basically turn the entries in field2 into a coma separated list of values sorted by field1.

Example 1:

field1_field2
1_____a
1_____b
1_____c
2_____f
2_____g

and I would like to get it in the form

Example 2:

field1_field3
1_____a,b,c
2_____f,g

View 2 Replies View Related







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