asked on

SQL Piviot Functionality

I have a table that contains many rows of data for a unique account number:
uid number phone_number
1 987 9725551212
2 987 9725551212
3 987 4695551212
4 987 2145551212

I need to remove the duplicates and create a single row of the data with the number and each unique phone_number.
number, phone_number1, phone_number2, phone_number3..... phone_number15

I cannot seem to get all the numbers uniquely.

petr_hlucin

Removing duplicates is quite easy (see attached code).

If you sort according to number (I suppose that number identifies the person whichs phone_number it is) you'll receive something like this:

uid number phone_number
1 987 9725551212
3 987 4695551212
4 987 2145551212

If you need a table like this:

uid number phone_number phone_number2 phone_number3 ...
1 987 9725551212 4695551212 2145551212 ...

... then you should either play with the data from the select below in Excel or write a MSSQL functions (eg. get_nth_phone_number(number, n))which will return nth phone_number for a specific number. Then you should select get_nth_phone_number(number, 1) as phone_number1 ... from the select in the code.

SELECT *
FROM table t
WHERE NOT EXISTS (
  SELECT * 
  FROM table t2
  WHERE t.uid > t2.uid
  AND t.phone_number = t2.phone_number)
)

Open in new window

ralmada

you can do something like this

;with CTE as (
	select distinct number, phone_number
	from yourtable
)
select distinct
	number,
	stuff((select ',' + phone_number from CTE where number = a.number for xml path('')), 1, 2, '') as phone_numbers
from CTE a

Open in new window

ASKER CERTIFIED SOLUTION

JuanchoVzla

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ralmada

If you want it in separate columns then you can do something like the below:

declare @cols varchar(max)
declare @strSQL varchar(max)

set @cols = stuff((select '], [' + cast(rn as varchar(10)) from (
			select 	distinct
			number, 
			phone_number,
			row_number() over (partition by number order by phone_number) rn
			from yourtable
			) a for xml path('')
		), 1, 1, '') + ']'



set @strSQL = 'select number, ' + @cols
		+ ' from (
			select 	distinct
			number,
			phone_number
			row_number() over (partition by number order by phone_number) rn
			from yourtable
		) o 
		pivot (max(phone_number) for rn in (' + @cols + ')) as p'

exec(@strSQL)

Open in new window

ralmada

correction in line 4 above

declare @cols varchar(max)
declare @strSQL varchar(max)

set @cols = stuff((select distinct '], [' + cast(rn as varchar(10)) from (
			select 	distinct
			number, 
			phone_number,
			row_number() over (partition by number order by phone_number) rn
			from yourtable
			) a for xml path('')
		), 1, 1, '') + ']'



set @strSQL = 'select number, ' + @cols
		+ ' from (
			select 	distinct
			number,
			phone_number
			row_number() over (partition by number order by phone_number) rn
			from yourtable
		) o 
		pivot (max(phone_number) for rn in (' + @cols + ')) as p'

exec(@strSQL)

Open in new window

txdolfan

ASKER

This solution worked right out of the gate (after table name mods) and ran really clean. Thank you all for the suggestions I really appreciate it.