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

Transact SQL :: How To Split Comma Separated Columns Into Separate Rows

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

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

SQL Server 2014 :: Separate Words To Rows (Convert Comma Separated)

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

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

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

SQL Server 2008 :: Data Conversion - Merge Multiple Columns Into Single Column Separated By Semicolons

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

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

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

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

Transact SQL :: Multiple Comma Separated Input For A Procedure

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

Integration Services :: Comma Separated Value In A Flat File With Two Columns (Code / Name)

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

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

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

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

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

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

SQL Server 2012 :: Comma Separated Column Output

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

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

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

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

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

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

T-SQL (SS2K8) :: Convert Column Data Into Comma Separated Data?

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

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

Returning Subsets Of The Same Column In Separate Columns...

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

Extracting Data From Columns That Contain Multiple Values Spearated By A Comma

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

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







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