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


ADVERTISEMENT

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

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

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

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

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

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

SQL Server 2012 :: Expand Comma Separated Values In Field In Select Statement

Jul 13, 2015

Consider the following data:

create table #test
(id int
,color varchar(20)
)
insert into #test
(id, color)
values
(1, 'blue'),(2, 'red'),(3,'green'),(4,'red,green')

if I wanted to run a query to select any records that had red in the color field, how would I do that? Not the one with only red, but a query that would give me both record number 2 and record number 4.

View 9 Replies View Related

Select Question - Codes In A Column Comma Separated

Mar 26, 2008

(OK, I guess bad table design, here's the question: )I have a table Buildings and one column is consultants. Inside this column are codes of another table Consultants separated with comma i.e. 0001, 0002, 0003, .... I want to select data from Buildings and last_names of Consultants in the same query.SELECT code, *other Building columns*, consultants_last_namesFROM Buildings Please help. 

View 6 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

SQL Server 2012 :: Comma Separated In A Single Result

May 30, 2014

I have the following table

CREATE Table #Table1
(
ID INT, Name VARCHAR(50), Class VARCHAR(10)
)
INSERT INTO #Table1
Select 1, 'name1', 'a' UNION ALL
Select 2, 'name1', 'a' UNION ALL

[Code] ....

Is it possible to have each name and its corresponding class in a single line separated by commas to give a result like the one below in #table2 ?

CREATE Table #Table2
(
ID INT, CommaSeparated VARCHAR(100)
)
INSERT INTO #Table2
Select 1, 'name1, a' UNION ALL

[Code] ...

What I have

Select * FROM #Table1

Final Result
Select * FROM #Table2

Note that I still want to see all the IDs regardless.

If that is not possible to see all the IDs, I think the results below in #Table3 should suffice.

CREATE Table #Table3
(
CommaSeparated VARCHAR(100)
)
INSERT INTO #Table3
Select 'name1, a' UNION ALL
Select 'name2, b, c, d' UNION ALL
Select 'name3, e, f'
Select * FROM #Table3

View 3 Replies View Related

Transact SQL :: How To Split Comma And Pipe Separated From Single Set

Oct 21, 2015

I have an input parameter of an SP which value will be passed with different combinations with 2 seperators (comma and pipe)

Value to the parameter is like this :      '10|22|microsoft,20|25|sql,30|27|server,40|29|product'

I want output like this

Column1       Column2      Column3
10                   22              microsoft
20                   25              sql
30                   27              server
40                   29              product

Pipe separator is for column and comma separator is for row.

I know if its a single separator, it can be done with function but how to do if its 2 separators?

View 6 Replies View Related

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

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

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

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

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

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

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

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

HOWTO Select Several Rows In One Comma- Separated

Aug 21, 2007

Hello!!

First of all, thank you in advance for helping me!!!

My problem is that I have a BBDD with a table like this (in Oracle and in MySql, both)

E.g.

+---------+-----------+
| groupId | serviceId |
+---------+-----------+
| grup1 | service1 |
| grup1 | service2 |
| grup1 | service3 |
| grup2 | service1 |
| grup2 | service2 |
+---------+-----------+

And I need to do a select o a procedure or something that returns me something like this:

+---------+------------------------------------------+
| groupId | serviceId |
+---------+------------------------------------------+
| grup1 | service1, service2, service3 |
+---------+------------------------------------------+

Is this possible in any way????

Than you very very much.

Almu

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

How To Select Multiple Rows As Comma Separated List?

Jul 4, 2004

Hi,

I have a table of users, a table of categories, and a many-to-many table linking users to categories.

My problem is that I want to select all the users with an extra column containing a comma-separated list of the categories they belong to.

Here is a stripped-down version of the table fields:

tbl_User
UserId, Email

tbl_Category
CatId, CatName

tbl_User_Category
UserId, CatId


I have tried using the coalesce function to build a string, but can only get this to work for one row at a time:

DECLARE @list nvarchar(100)

SELECT @list = COALESCE(@list + ', ', '') + CAST(CatId AS varchar(4))
FROM tbl_User_Category
WHERE UserId = @UserId

SELECT @list as List


Any ideas on how to add to this to get it to do each row in tbl_Page? Or am I attacking this from the wrong angle?????

Any help would be fantastic!

thanks,
Rob

View 4 Replies View Related

SQL Server 2014 :: Split Out A Field Of Comma Separated Values Based On Unique Code In Same Row?

Oct 21, 2014

I have a comma separated field containing numerous 2 digit numbers that I would like splitting out by a corresponding unique code held in another field on the same row.

E.g

Unique Code Comma Separated Field

14587934 1,5,17,18,19,40,51,62,70

6998468 10,45,62,18,19

79585264 1,5,18

These needs to be in column format or held in an array to be used as conditional criteria.

Unique Code Comma Separated Value

79585264 1

79585264 5

79585264 18

View 5 Replies View Related







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