Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

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.
0
txdolfan
Asked:
txdolfan
1 Solution
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now