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.
txdolfanAsked:
Who is Participating?
 
JuanchoVzlaCommented:
Hi,
You can do it using pivot table, but you need to know the maximum amount of phone number in order to change this query:
SELECT number,
	[1],
	[2],
	[3],
	[4],
	[5]
FROM 
(
	SELECT number, phone_number, RANK() OVER (PARTITION BY number ORDER BY phone_number) AS rnk
	FROM TB1
	GROUP BY number, phone_number
) AS orgData
PIVOT
(
	MIN(phone_number)
	FOR rnk IN (
		[1],
		[2],
		[3],
		[4],
		[5])
) AS pvtTabl

Open in new window

This one is set to a maximum of 5 phone numbers, if you want more, just add the columns i both, the select and the pivot clause

Hope it helps
0
 
petr_hlucinCommented:
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

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

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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

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

0
 
txdolfanAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.