Seperated record when there is more than 1 value in column

Question

I have a requirement where I have to write a SQL query to produce seperate records if my column contains more than one value.

Like

select * 
from tblMachine 
where pkey = 1;

This is the result I got:

1 C000279   EMAIL PIC       xx@yy.com;yy@xx.com

I have a requirement to write a query so that it will produce seperate record if there are more than 1 value in the email column.

Like

1 C000279   EMAIL PIC       xx@yy.com
1 C000279   EMAIL PIC       yy@xx.com

Is this possible in SQL Server 2008?

@GurV, this is my exact query

select distinct c.PKEY [Customer Code],
c.NAME [Customer Name],
mi.MODEL [Machine #],
(select Split.a.value('.','varchar(100)') email
from 
    (select       
        cast('<x>' + replace(m.CONTACT_EMAIL,';','</x><x>')+'</x>' as XML) as x
    from tblMachineContact m
    where mi.PKEY=m.MAC_PKEY and m.CONTACT_CATEGORY_PKEY in ('PC'))t
cross apply x.nodes ('/x') as Split(a)) [Customer Email]
from tblMachine mi
inner join tblCustomers on m.CUST_PKEY=c.PKEY
where mi.STATUS='A'

You should **NEVER EVER** store more than one value into a single column! This is a **HORRIBLY BAD** practice and violates even the first normal form of database design - **YOU DON'T DO IT!** If your `tblMachine` needs to have 1:n e-mails associated with it, you need to use a separate `tblEmail` table with a foreign key relationship to `tblMachine` - that's the proper relational design — marc_s, Feb 01 '17 at 07:49
I Think that what you want: https://blog.sqlauthority.com/2015/04/21/sql-server-split-comma-separated-list-without-using-a-function/ — ahmed abdelqader, Feb 01 '17 at 07:59
@marc_s Dear LT268, I'm sure this question is bound to the need to do exactly this step towards normalisation (*deep pedagogical eye contact*) — Shnugo, Feb 01 '17 at 08:58

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

You should not store multiple values in one single row and column. Consider normalizing your table structure.

For now, you can do this using cross apply and XML. I used column names as col1, col2, ... as you've not provided any schema details. Please change them as you need.

with your_table(col1     , col2   , col3, col4) as (
    select 1 ,  'C000279'  ,'EMAIL PIC' , 'xx@yy.com;yy@xx.com'
) 
--- test data. Don't mind the above. ---

select col1, col2, col3,
    Split.a.value('.','varchar(100)') email
from 
    (select 
        col1, col2, col3,
        cast('<x>' + replace(col4,';','</x><x>')+'</x>' as XML) as x
    from your_table) t
cross apply x.nodes ('/x') as Split(a);

Produces:

EDIT:

Try this:

select distinct 
    c.PKEY [Customer Code],
    c.NAME [Customer Name],
    mi.MODEL [Machine #],
    m.email [Customer Email]
from tblMachine mi
inner join tblCustomers on m.CUST_PKEY=c.PKEY
left join (
    select
        MAC_PKEY,
        Split.a.value('.','varchar(100)') email
    from (
        select       
            MAC_PKEY,
            cast('<x>' + replace(m.CONTACT_EMAIL,';','</x><x>')+'</x>' as XML) as x
        from tblMachineContact
        where CONTACT_CATEGORY_PKEY = 'PC'
    ) t cross apply x.nodes ('/x') as Split(a)
) m on mi.PKEY=m.MAC_PKEY
where mi.STATUS='A'

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 01 '17 at 07:51

Gurwinder Singh

38,557
6
51
76

Bear in mind, that the simple XML-split will break, if there are forbidden characters (like the `&`-character) within the eMail address ([one related question about allowed characters](http://stackoverflow.com/questions/2049502/what-characters-are-allowed-in-email-address)). – Shnugo Feb 01 '17 at 08:51
You might [read this](http://stackoverflow.com/a/41105065/5089204) to find a secure way for escaping... – Shnugo Feb 01 '17 at 08:52
@GurV your code works for me, unfortunately i am using this part as a subquery inside main select statement. So when the subquery returns more than 1 value, the error occurs :( – LT268 Feb 02 '17 at 01:12
@LT268 - Can you post the query you're trying to use in the question? – Gurwinder Singh Feb 02 '17 at 03:47
@GurV, i had posted my query in question. pls refer – LT268 Feb 02 '17 at 03:53
@LT268 - You'll need a JOIN. Updated the answer. Please have a look. – Gurwinder Singh Feb 02 '17 at 04:12
That is a totally different question. You can directly do that without all this. You can use regular expressions to divide the column into two or more. I'd suggest you ask a new question for that. – Gurwinder Singh Feb 02 '17 at 05:41
@GurV- Great !! Working fine – LT268 Feb 02 '17 at 06:53

Seperated record when there is more than 1 value in column

1 Answers1

EDIT: