multiple rows of data w/ duplicate records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

multiple rows of data w/ duplicate records

Author

Topic

spitfire122
Starting Member

11 Posts

Posted - 2011-07-07 : 13:41:04

I have a loan_number that i only want to show once.
each loan_number has multiple phone numbers. the phone numbers table is built like if there are 5 phone#'s for a loan_number, the same loan_number will be listed 5 times with each phone#.

how do i join from the phone number table so I can see only the loan number once? but i want all the phone numbers in their own column

example:

loan# column1 column2 column3
------ -------- --------- ---------
1234 phone#1 phone#2 phone#3

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-07 : 13:57:51

Here is an example of how you can do it using PIVOT. You need to be on SQL 2005 or higher to be able to use this:


CREATE TABLE #tmp (loan_no INT, phone_no INT);

INSERT INTO #tmp 
SELECT 1,1234 UNION ALL
SELECT 1,4566 UNION ALL 
SELECT 2,5566 UNION ALL 
SELECT 2,6566 UNION ALL 
SELECT 2,7566;

WITH cte AS
(
   SELECT *,ROW_NUMBER() OVER (PARTITION BY loan_no ORDER BY phone_no) AS RN
   FROM #tmp
)
SELECT
   *
FROM
   cte
PIVOT
( MAX(phone_no) FOR RN IN([1],[2],[3],[4],[5])) P

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources