How to create separate rows for each unique value in source data

Question

I have following table:

Cus_ID  Work_Phone     Home_Phone     Mobile_Phone
1       x              Blank          x
2       x              x              Blank
3       x              x              x
.
.
. and so on (1000s of rows)

Work_Phone, Home_Phone, Mobile_Phone - varchar

x = some value present

I need to select from Source data to move it Target system like below, I need to create separate row for unique values for each Cus_ID. How do i do it?

Cus_ID    Type      ContactNo
1         Work       x
1         Mobile     x
2         Work       x
2         Home       x
3         Work       x
3         Home       x
3         Mobile     x

.. and so on

Type, ContactNo - varchar

x = Should be the corresponding value from Source table

Sorry guys for not being so detail earlier, I edited to include much more detail of the problem. Please help. — KTP
– KTP, Commented Jun 9, 2015 at 14:01

mohan111 · Accepted Answer · 2015-06-09 01:48:35Z

1

above result we can achieve using UNPIVOT or Cross Apply also by basing on your assumed data

declare @t table (PK varchar(1),col1 varchar(1),col2 varchar(1),col3 varchar(1))
      insert into @t(PK,col1,col2,col3)values 
        ('X','a','','c'),
        ('y','a','b',''),
        ('z','a','b','c')

Cross Apply :

select PK,value
from @t
cross apply
(
    values
        ('I1', col1),
        ('I2', col2),
        ('I3', col3)
) c(col, value)
where value is not null AND value <> ''
order by PK, col

UNPIVOT

select PK,value
from @t
unpivot
(
  value
  for col in (col1, col2, col3)
) un
WHERE value <> ''
order by PK, col;

answered Jun 9, 2015 at 1:48

mohan111

8,9054 gold badges35 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike K · Accepted Answer · 2015-06-10 22:29:50Z

0

Assuming col1, col2 and col3 are of the same type, then:

SELECT pk, col2 AS target_value FROM your_table WHERE col2 IS NOT NULL
UNION 
SELECT pk, col3 AS target_value FROM your_table WHERE col3 IS NOT NULL
UNION 
SELECT pk, col4 AS target_value FROM your_table WHERE col4 IS NOT NULL
ORDER BY pk

Edit edit: here's the version with ISNULL tests, column headings and the rest, in response to your revised question:

SELECT Cus_ID, 'Work' AS Type, Work_Phone AS ContactNo FROM your_table
   WHERE ISNULL(Work_Phone, '') <> ''
UNION 
SELECT Cus_ID, 'Home' AS Type, Home_Phone AS ContactNo FROM your_table
   WHERE ISNULL(Home_Phone, '') <> ''
UNION
SELECT Cus_ID, 'Mobile' AS Type, Mobile_Phone AS ContactNo FROM your_table
   WHERE ISNULL(Mobile_Phone, '') <> ''
ORDER BY 1

If there's a chance the "blank" column may contain whitespace characters, then refine it yet further to:

... ISNULL(LTRIM(Work_Phone), '') <> ''

etc.

edited Jun 10, 2015 at 22:29

answered Jun 8, 2015 at 23:40

Mike K

4863 silver badges7 bronze badges

3 Comments

Mike K Over a year ago

What dupes? But you're right, the ALL is unnecessary (though harmless). I've corrected the code. Thanks.

Mike K Over a year ago

See newly edited answer. Hope that's enough. I've used ISNULLs to allow for 'blank' phone numbers to be either null or blank, without breaking.

KTP Over a year ago

I achieved the results by using your updated query . Thank you!

Collectives™ on Stack Overflow

How to create separate rows for each unique value in source data

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related