CODEWITHSUNDEEP
sqlsql-server
LT268
LT268

Reputation: 135

Seperated record when there is more than 1 value in column

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       [email protected];[email protected]

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       [email protected]
1 C000279   EMAIL PIC       [email protected]

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'

Upvotes: 0

Views: 79

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39517

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' , '[email protected];[email protected]'
) 
--- 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:

enter image description here

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'

Upvotes: 2

Related Questions