Query Result As Comma-separated List

Jul 23, 2005

Hi,
I'n in an environment where I cannot make stored procedures. Now I need
to make a query with a subquery in the SELECT part which gives a comma
separated list of results:

SELECT
p.id,
listFunction(SELECT name FROM names WHERE name_parent=p.id) AS
'nameList'
FROM projects AS p

This query should return something like:
1, "john,mike,petra"
2, "bob,carl,sandra,peter,
etc

listFunction is (of course) not (yet) defined. Is this possible without
the use of stored procedures?
Mike

View 12 Replies


ADVERTISEMENT

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

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

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

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

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

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

Transact SQL :: Powershell For Pass Comma Separated List

Sep 8, 2015

I have a requirement, we need to pass comma separated list using powershell script.

How can we achieve the above scenario?

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

Function To Create Comma Separated List From Any Given Column/table.

Jul 20, 2005

Hi,I'm sure this is a common problem.. to create a single field from awhole column, where each row would be separated by a comma.I can do this for a specified table, and column.. and I've created afunction using VBA to achieve a more dynamic (and very slow) solution..so I would like to implement it using a user defined function in sql server.The problems I'm facing are, that I can't use dynamic sql in afunction.. and I also can't use temporary tables which could build up a'standard' table from parameters given to then perform the function on.So, with these limitations, what other options do I have?Cheers,Chris

View 1 Replies View Related

Transact SQL :: Create Comma Separated List For Each Account And Date?

Jun 19, 2015

i have the following:

DECLARE @Table TABLE (
 OrgRoleNumTxt VARCHAR(10)
, AccountNm varchar(100)
, EffectDate DATETIME
, OperationNm varchar(100)
, Premium decimal(18,2)
)
Insert into @Table(OrgRoleNumTxt, AccountNm, EffectDate, OperationNm, Premium) VALUES
('00236', 'R.R. Donnelley', '2010-01-01', 'Chicago', 1000),
('00236', 'R.R. Donnelley', '2010-01-01', 'Boston', 3000)
select *
from @Table

but want this

Is it possible using basic T-SQL?

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

T-SQL (SS2K8) :: Creating Comma Separated List Of Details From Multiple Columns?

Jun 3, 2010

I am trying to find a way to add into a table a flattened (comma seperated list) of email addresses based on the multiple columns of nformation in another table (joined by customer_full_name and postcode.

This is to highlight duplicate email addresses for people under the same customer_full_name and Postcode.

I have done this using a loop which loops through concatenating the email addresses but it takes 1minute to do 1000. The table is 19,000 so this isn't really acceptable. I have tried temp tables, table variables and none of this seems to make any difference. I think that it is becuase i am joining on text columns?

Create table #tempa
(
customer_Full_Name varchar(100),
Customer_Email varchar(100),
Postcode varchar(100),
AlternateEmail varchar(max)NULL
)
insert into #tempa (customer_full_name,customer_email,postcode)

[code]....

View 9 Replies View Related

Transact SQL :: Get Multiple Rows Based On Comma-separated Ntext List In On Column?

Jun 2, 2015

I have a table that is used to build rules. The rules point to other columns in other tables and usually contain only one value (i.e. ABC). But one of the options is to add a comma-separated list of SSNs (i.e. 123123123,012012012,112231122).  I am trying to build a single query that allows me to leverage that list to get multiple rows from another table.

This obviously works:

SELECT * FROM vw_Person_Profile P (NOLOCK)
WHERE P.PrsnPIISSN_Chr IN ('123123123','012012012','112231122')

But this does not:

SELECT * FROM vw_Person_Profile P (NOLOCK)
WHERE P.PrsnPIISSN_Chr IN (
SELECT '''' + REPLACE(CONVERT(VARCHAR(4000),txtFieldValue), ',', ''',''') + ''''
FROM MassProcessing_Rules PR
WHERE PR.intRuleID = 10
)

View 5 Replies View Related

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

SQL 2012 :: Pass List Items To Stored Proc As Comma Separated Parameter - Foreach Loop

Feb 11, 2015

I have a multiselect checkbox list in my UI. I pass the list items to my stored proc as comma separated parameter and then I have a function which converts this parameter to a table with separate rows.

E.g. : a,b,c,d

Converted to result table

result
a
b
c
d

I want to insert each row of the result table into another table. How to do that.

E.g., the table after the function is :

CREATE TABLE #result
(
Subject varchar(100)
)

insert into #result values ('a')
insert into #result values ('b')
insert into #result values ('c')
insert into #result values ('d')

So the pseudo code is something like

for each row in #result

insert row into another table

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

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

Comma In Value Within Comma Separated File

Apr 10, 2006

I have a file which contains comma separated columns. One of columns contains names of companies. Sometimes the names of the companies have a comma as part of the name. For those, the value is surrounded by double-quotes.

But it seems that SSIS ignores the double quotes and ONLY looks for the column separator. This causes my value to be split in half.

Traditionally, I thought parsers that deal with this type of import do not automatically take the first comma following the double-quote as the column separator but instead look for the first comma following the ending quote. (i.e. Look at how Excel performs imports...)

I cannot set the column separator of the column to double-quote comma since only those values that HAVE a comma in them are qualified.

Any ideas?

Here is sample fie content to see what I mean:

342123, Jason, 12345
21, Kim,4567
32.43, John Paul, 1245
23, "Mr. T", 98764
12, "Peter, Paul, Mary", 09643

The last entry should be imported as 12 in the first column, "Peter, Paul, Mary" in the second column and 09643 in the third but instead ends up as 12 in the first, "Peter in second column and Paul, Mary", 09643 in the last.

(Oddly enough, if I remove the first column of numbers the import works like it is supposed.)

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

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

Comma Separated Value

Feb 5, 2004

Suppose I have a table like this

code Value
1 a
1 a
1 b
2 c
2 c
1 d
2 g


Now my require ment that I want a distinct comma separated report about these data.Means for code 1 I need a comma separated distinct values.In this case it should be a,b,d
My output should be like this
1 a,b,d
2 c,g
Can anybody help me I can I do this with the help of a cursor or any other way?
Subhasish

View 1 Replies View Related

Comma Separated Value

Jun 26, 2007

I’m passing a comma separated parameter (home, world, child) into stored procedure.
I have a Slitter function which is basically creates a table out of delimited list.

My stored procedure needs to find matched records in one of the table based on delimited list.

I have something like this:

SELECT *
FROM Word
WHERE WordName IN (SELECT * FROM dbo.fxSplitter('home,world,child, ',')

I would like to have my stored procedure be able to select rows, even if comma delimited parameter holds part of the name like this “hom, wor, chil� .
Another words it will be SELECT * FROM Word WHERE WordName LIKE '%hom%' OR WordName LIKE '%wor%' OR WordName LIKE '%chil%'

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

Find In Comma Separated Value

Aug 20, 2004

Hi,

****SQL Server related question.


I have a table in which one of the columns (col1) holds a string, like: 1,2,3,4,5,6,7,8,9,10

I am passing an int value (@intValue) to the sproc.

What I want to be able to do is query the table like....

SELECT * FROM myTable where @intValue .... is in col1

Any ideas?


Thanks a lot!!!!

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

Comma Separated Value Statement

Oct 15, 2013

I'm trying to see the following comma separated sql statement using 'print' but it is throwing error "incorrect syntax near ','".

declare @csv varchar(max)
set @csv = '535,232'
print ''Select *
from tbl
where ',' + @csv + ',' like '%,' + mainid + ',%'''

View 10 Replies View Related

Comma Separated In Where Clause

Jun 6, 2014

What the difference between the following two codes in where clause?

The both provides the same resultset for me.

alter procedure
@fieldname varchar(50)
as
begin
select field1, field2 from table1
where ',' + @fieldname + ',' like '%,' + field3 + ',%'
end

The second code :

alter procedure
@fieldname varchar(50)
as
begin
select field1, field2 from table1
where @fieldname like '%' + field3 + '%'
end

View 2 Replies View Related

Comma Separated To Columns

Apr 10, 2015

I have Oracle query which seperates a text with commas to column data. Can we achieve this in SQL Server?

with t as (select 'abcd,123,defoifcd,87765,aoiwerwe' as str from dual)
select level as n, regexp_substr(str,'[^,]+',1,level) as val
from t
connect by regexp_substr(str,'[^,]+',1,level) is not null;

N VAL
1abcd
2123
3defoifcd
487765
5aoiwerwe

I'm working on SQL Server 2012, Windows 7.

View 1 Replies View Related







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