In SSIS, What Is The Best Way To Take A Column With Comma Separated Strings And Separate Them To Multiple Columns
Jul 10, 2006
Hi There,
Can anybody suggest me what is the best way to take a column with comma separated stings and output them into multiple columns with strings?
for example if I have a column with "watertown, newton" as a string, I need to separate them to two columns with watertown and newton values?
Is Derived column transformation the best way to do it?
Thanks.
Sam.
View 6 Replies
ADVERTISEMENT
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
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 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
May 3, 2015
I have a table Sample with data stored like below
ID|STRING |
------------------------------------------------------------------
1| 'ENGLAN SPAIN' ITALY 'FRANCE GERMANY' BRAZIL
I need the output like..
-----------------
|ENGLAND SPAIN |
|---------------|
|ITALY |
|---------------|
|FRANCE GERMANY |
|---------------|
|BRAZIL|
-----------------
How can I do the same with a select query in SQL Server?
View 4 Replies
View Related
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
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
Oct 19, 2015
I'm working on a script to merge multiple columns(30) into a single column separated by a semicolons, but I'm getting the following error below. I tried to convert to the correct value. but I'm still getting an error.
Error: "Conversion failed when converting the varchar value ';' to data type tinyint".
select
t1.Code1TypeId + ';' +
t1.Code2TypeId + ';' +
t1.Code3TypeId + ';' +
t1.Code4TypeId as CodeCombined
from Sampling.dbo.account_test t1
where t1.Code1TypeId = 20
or t1.Code2TypeId = 20
or t1.Code3TypeId = 20
or t1.Code4TypeId = 20
View 4 Replies
View Related
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
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 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
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 one requirements below..
Table input
Eno       ename                 Eloc      Â
Edept
1             Sid                        Â
Pune    101,201,301,401,501,601
Output:
Eno       ename                 Eloc      Â
Edept
1             Sid                        Â
Pune    101
1             Sid                        Â
Pune    201
1             Sid                        Â
Pune    301
1             Sid                        Â
Pune    401
1             Sid                        Â
Pune    501
1             Sid                        Â
Pune    601
View 5 Replies
View Related
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
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
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
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
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
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
Nov 19, 2013
I need a query which results the output as comma separated
for example ,
select name from tablename;
say for example this produces the following,
Name
Jack
John
Smith
I need the follwoing,
Jack,John,Smith
View 2 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
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
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
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
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
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
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
Mar 14, 2014
I have data in the below format .
NameValuecategory
AAA510
BBB510
CCC510
DDD512
EEE512
FFF512
I want the result in the below format
NAMEValuecategory
AAA,BBB,CCC510
DDD,EEE,FFF5120
I have tried stuff but all six values(AAA...FFF) are coming in one row , however i need them as per the category.
View 2 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
Mar 23, 2006
I have the following 2 Sql queries. They both are rowcounts of the same column but based on different criteria. What I want to do is return the two results side by side in separate columns:
-- Subscriptions since Sept. 24th
SELECT count(*)
FROM SiteMemberTable103 s(nolock)
JOIN clientmembertable25 c(nolock) ON s.memberid = c.memberid
WHERE site_firstjoindate is not null
and c.clientunsubscribe = 0
and c.validemailaddr = 1
and s.unsubscribe = 0
-- Subscriptions in February
SELECT count(*)
FROM SiteMemberTable103 s(nolock)
JOIN clientmembertable25 c(nolock) ON s.memberid = c.memberid
WHERE site_firstjoindate BETWEEN '2006-02-01 00:00:00.000' AND '2006-03-01 00:00:00.000'
AND c.clientunsubscribe = 0
AND c.validemailaddr = 1
AND s.unsubscribe = 0
It seems like a UNION ALL should work but it just returns the results in one column. I tried changing the count by specifying a different column for each but that doesn't work either. I also tried writing it as one query and using alias to differentiate the two tables but that just gives me syntax errors. I suspect there is a more elegant way to do this but I'm at a loss. Any help would be greatly appreciated!
Caeanis
View 1 Replies
View Related
Nov 11, 2004
Hi all,
If I have a column in one table (contracts) that contains a set of values (codes that identify the fields contract code) like as follows...
Code:
Contracts table
--------------------------------
Concode - description
--------------------------------
KIDD - Kidderminster General
UNIV - University Hospitals
and then another table (controls) which has a column called contracts where the data within the 'Contracts' field is set out like the following (yes that is right, each of the data in this column are the Foreign Keys which are separated by '/' in which I need to query against the contracts table ) ...
Code:
controls table
--------------------------
Code - Contracts
--------------------------
BA - KIDD /UNIV /NWPCT
With the those tables and the columns like they are, how can I get my query to display the following information...
PHP Code:
---------------------------------------------------
Con(Controls table) - Description(contracts table)
---------------------------------------------------
BA - Kidderminster General
BA - University Hospitals
---------------------------------------------------
I'm guessing my first job is to somehow extract those Foreign Keys from the 'Contracts' column in the 'Control' table
Tryst
View 7 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
