Solved

# SQL Piviot Functionality

Posted on 2011-05-12
193 Views
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
Question by:txdolfan

LVL 4

Expert Comment

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)
)
``````
0

LVL 41

Expert Comment

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
``````
0

LVL 3

Accepted Solution

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
``````
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

LVL 41

Expert Comment

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)
``````
0

LVL 41

Expert Comment

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)
``````
0

Author Closing Comment

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.