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
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Jun 12, 2015
I am try to use a variable containing comma separated values in the IN clause.
View 10 Replies
View Related
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
Jul 14, 2015
The Input parameters will be supplied like below for a procedure.
DECLARE @Subject NVARCHAR(100) = 'Math, Physics, Science'
   ,@ClassNumber NVARCHAR(MAX) = '102,103|415,206|712,876'
/*
I need to select subject with classnumber's supplied from the input screen and output a result like this.Let's say I have a temporary table dbo.TableA
--For  Math
INSERT INTO dbo.TableA
SELECT @Subject FROM Class
WHERE @ClassNumber in (102,103)
[code]....
View 6 Replies
View Related
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
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
Sep 14, 2015
I have values in two columns separated by commas, like shown below:
I need the Output Like
How to do this in SQL server ?
View 6 Replies
View Related
Jul 16, 2015
I am need to create comma separated list in sql and I am using below query.
declare @ConcatenatedString NVARCHAR(MAX)
select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR)
from
(
select 1 Number
union
select 2 Number
union
select 3 Number
)rslt
select @ConcatenatedString
When I use the above code inside a function, i am not getting desired output.
create function GetConcatenatedValue
AS
(
declare @ConcatenatedString NVARCHAR(MAX)
select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR)
from
(
select 1 Number
union
select 2 Number
union
select 3 Number
)rslt
return @ConcatenatedString
)
View 3 Replies
View Related
Jul 2, 2015
Picture tells all what i need. Anyway i want to combine upper two tables data like below result sets. Means they should be grouped by bsns_id and its description should be comma separated taken from 2nd table. In sql server 2012 ....
View 5 Replies
View Related
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
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
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
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
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
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
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
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
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
