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


ADVERTISEMENT

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

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

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

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

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

Transact SQL :: Split Comma Separate Value And Then Lookup ID

May 16, 2015

I have a table that has some comma separated values and then have another table with the lookup values. I"m not sure why it was created this way and have not had to do this. This is a vendor database so I can't go around and changing things. 

Table A 
IDStageIDs
188
288,86,87
388,87

Table B (Lookup table)
IDName
86test1
87test2
88test3

Expected results
StageIDsResult
88 test3
88,86,87test3, test1, test2
88,87 test3, test2

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

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

Transact SQL :: Keep Entire Columns Into 2 Rows When There Is A Comma In A Column

Nov 2, 2015

I have a table and one of the column have tab delimited value and I need to separate the tab delimited values and keep them in separate rows.

ID       Name                  State      Country
1       Scott, Ricky            NSW       AUS
2       Martin                   VIC         AUS
3    James, Peter,John     WA        AUS

ID column is not a primary key. I want the output columns to identify the comma (,) and put them in separate rows as below

ID       Name                  State      Country
1       Scott                     NSW       AUS
1       Ricky                     NSW       AUS
2       Martin                   VIC         AUS
3      James                     WA        AUS
3      Peter                      WA        AUS
3      John                       WA        AUS

How to strip the camma(,) in SQL?

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

Split Function To Separate Comma Delimited Values Into A Table

Oct 21, 2012

I have a stored procedure that is passed two values. Both are strings representations of GUID values, the first is a single value, the second is a comma delimited string of values.In the stored procedure I call a split function to separate the comma delimited values into a table and this is used in my WHERE clause to filter my select results.This is an example:

Code:
SELECT
item.uiGUID as ItemGUID,
stores.strStoreName as Store,
location.strLocationName as Location
FROM tblItems as item
INNER JOIN tblStoreLocations as location
ON item.uiLocationGUID = location.uiGUID
INNER JOIN tblStores as stores
ON location.uiStoreGUID = stores.uiGUID
WHERE CAST(item.uiGUID as varchar(36)) IN (SELECT Value FROM dbo.Split(',',@ItemGUIDList))

When I run this query in the management studio, passing a list of 5 values in the second parameter, my results include one item for each of the 5 values. However, when I pass the parameters from my ASP project, (I've verified the values I think are being passed are indeed being passed), I only get one item.I believe the error is in my split function. Both split functions return the same results in the SQL management studio, but only one returns the correct results in the the ASP project.

When I use this version of the function it returns the correct table values to the calling application, but it chokes when the item list does not have a trailing comma. I figure that to be a bug in the SQL function.

Code:
CREATE FUNCTION [dbo].[Split]
( @Delimiter varchar(5),
@List varchar(8000)
)
RETURNS @TableOfValues table
( RowID smallint IDENTITY(1,1),
[Value] varchar(50)

[code]....

View 7 Replies View Related

Transact SQL :: Split Rows By Day / By Datetime And Partition By Columns

Jul 22, 2015

I am trying to spilt records into days by the start - End datetime.

I would send an image and data but because I am new to the forum, I am blocked sending images.

"Body text cannot contain images or links until we are able to verify your account"

How I can forward an image.

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

Comma Separated Cell Into Rows

May 13, 2008

Hello!

We are on SqlServer 2005.

Let me point out at the beginning that I don't have anyway to normalize this structure or get the admins to change the way the data is stored. We don't own the database where this is housed...we're just given the information via an .xls file...which we import to a SQLServer table.

I have some data that is given to me that has two columns (below is for an example):
Column A is an identifiying number, i.e. for a project
Column B is a comma separated list of account strings for the project

A sample layout of what we get via the .xls file might look like the following (Column A is to the left of the dashes, and Column B is to the right of the dashes):

AA.ProjectBuildTower ----- 2222, 3333, 4444, 5555
BB.ProjectBuildFence ----- X900, 6789, 9000, 9876

What I need to do is now haveprojects listed out in Column A with each of it's account strings in Column B like so:
AA.ProjectBuildTower ----- 2222
AA.ProjectBuildTower ----- 3333
AA.ProjectBuildTower ----- 4444
AA.ProjectBuildTower ----- 5555

BB.ProjectBuildFence ----- X900
BB.ProjectBuildFence ----- 6789
BB.ProjectBuildFence ----- 9000
BB.ProjectBuildFence ----- 9876

Any suggestions would greatly help!

Thanks!

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

Data Row Comma Separated Cell To Many Rows

May 13, 2008

Hello!

We are on SqlServer 2005.

Let me point out at the beginning that I don't have anyway to normalize this structure or get the admins to change the way the data is stored. We don't own the database where this is housed...we're just given the information via an .xls file...which we import to a SQLServer table.

I have some data that is given to me that has two columns (below is for an example):
Column A is an identifiying number, i.e. for a project
Column B is a comma separated list of account strings for the project

A sample layout of what we get via the .xls file might look like the following (Column A is to the left of the dashes, and Column B is to the right of the dashes):

AA.ProjectBuildTower ----- 2222, 3333, 4444, 5555
BB.ProjectBuildFence ----- X900, 6789, 9000, 9876

What I need to do is now haveprojects listed out in Column A with each of it's account strings in Column B like so:
AA.ProjectBuildTower ----- 2222
AA.ProjectBuildTower ----- 3333
AA.ProjectBuildTower ----- 4444
AA.ProjectBuildTower ----- 5555

BB.ProjectBuildFence ----- X900
BB.ProjectBuildFence ----- 6789
BB.ProjectBuildFence ----- 9000
BB.ProjectBuildFence ----- 9876

Any suggestions would greatly help!

Thanks!

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

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

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

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

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

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

Transact SQL :: Creating Comma Separated Value Logic Not Working Inside Function?

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

Transact SQL :: Merge Two Tables Data On Behalf Of ID In Comma Separated Manner?

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

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

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

Transact SQL :: Split A Column Which Contains Ntext Values Separated With Value

Jul 13, 2015

SQL syntax to split a column that contains ntext values that are separated with this value : ;#

I need it to create 3 new columns

i.e.: I have a column that contains values such as:

ROW1: ;#typea;#typeb;#typec;#typed;#
ROW2: ;#typeb;#typec;#
ROW3: ;#typeb;#

I need it to convert to 3 new columns and strip out the ;#:

E.g.:

Column 1
typea
typeb
typeb

Column 2
typeb
typec
blank

View 16 Replies View Related

SQL 2012 :: Split CSV Records To Separate Columns

Jun 12, 2014

I've a table as below

custid,companyname,phone,address
2,AAAAA,(222) 222-2222,address 2
3,cust 3,(333) 333-3333,address 3
5,BBBBB,(333) 333-3333,DDDDD
6,cust 6,(222) 222-2222,address 6
7,cust 7,(222) 222-2222,address 7

How to split csv values to new fields. so that the desired output should be as below

custidcompanynamephone address
2 AAAAA (222) 222-2222 address 2
3 cust 3 (333) 333-3333 address 3
5 BBBBB (333) 333-3333 DDDDD
6 cust 6 (222) 222-2222 address 6
7 cust 7 (222) 222-2222 address 7

View 9 Replies View Related

Transact SQL :: How To Split Comma Delimited String

Oct 8, 2008

I have a parameter called Id in my SP which will be of nvarchar data type and i'm going to get the multiple ids at a time seperated by commas in that parameter from the application. Now my requirement is to update a table with these comma seperated ids in seperate rows.

For example, if i have 2 parameters called Id1 and Id2. Id1 will contain only one value and will be of int data type and Id2 will be of nvarchar data type as i can get multiple ids delimited by a comma from the application.

Suppose Id1 = 1 and Id2 = '1,2,3,4'. Then I have to update id2 in the tables seperately like wherever Id1 is '1' i need to update Id2 column for 4 rows with the value 1, 2, 3, 4 respectively in different rows.
 
how can i do this in T-SQL? How can i split the data of parameter Id2 in 4 different rows?

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







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