1

I am new here so please bear with me if this type of question has already been asked, and I am going to try and be as clear as possible. With that said, I am at whits end trying to transform the data table below into the format required. All ideas are helpful.

PhType        PhNumber       Sequence
-------------------------------------
Cell Phone    111-222-3333      2
Cell Phone    222-333-4444      5
Home Phone    999-222-1111      6
Home Phone    555-444-3333      8

And I am trying to transform the data into this table.

**CellPhone1#**  **Sequence**  **CellPhone2#**  **Sequence**  **HomePhone1#** **Sequence**  **HomePhone2#**  **Sequence**                                                                              
111-222-3333  ----  2  ------        222-333-4444 -----     5    ---------      999-222-1111 ----- 6     -------     555-444-3333   ----------      8

The idea is turning rows of data into columns, but because the sequences on the numbers aren't in sequential order I am having a hard time doing this without gaps in my data. Currently I am transforming the information into column on the join level 

LEFT OUTER JOIN
    Reports.dbo.BorrowerTelephones BT with(nolock) ON B1.HHNbr = BT.HHNbr
                                                   AND B1.NamSeq = BT.NamSeq
                                                   AND BT.Seq = 0
                                                   AND BT.PhType = 'Cell Phone'
LEFT OUTER JOIN 
    Reports.dbo.BorrowerTelephones BT1 with(nolock) ON B1.HHNbr = BT1.HHNbr
                                                    AND B1.NamSeq = BT1.NamSeq
                                                    AND BT1.Seq = 1
                                                    AND BT1.PhType = 'Cell Phone'

However if there is not a phone number in sequence 0 or 1 of the cell phone phtype it will leave those columns blank. I need the query to take the sequences out of the equation altogether without repeating the phone number in the next column over. Please help.

Thanks, Mitch

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mitchel
  • 13
  • 4

1 Answers1

0

So, as I understand it, you're just wanting to collapse the data in your table down to a single row, correct? I have written a SQL query that tests what I think you're wanting using ROW_NUMBER() to handle the gaps in your sequence numbers. I hope this gives you an idea of what you'll need.

CREATE TABLE #testPhones (
    PhType VARCHAR(20),
    PhNumber VARCHAR(12),
    PhSequence INT)

INSERT INTO #testPhones(PhType, PhNumber, PhSequence)
VALUES('Cell Phone', '111-222-3333', 3),
      ('Cell Phone', '222-333-4444', 5),
      ('Home Phone', '999-222-1111', 6),
      ('Home Phone', '555-444-3333', 8)

SELECT ROW_NUMBER() OVER(ORDER BY PhSequence) AS Row, *
FROM #testPhones

SELECT A.PhNumber AS CellPhone1#, A.PhSequence AS PhSequence, 
    B.PhNumber AS CellPhone2#, B.PhSequence AS PhSequence, 
    C.PhNumber AS HomePhone1#, C.PhSequence AS PhSequence, 
    D.PhNumber AS HomePhone2#, D.PhSequence AS PhSequence
FROM (SELECT PhNumber, PhSequence FROM (SELECT ROW_NUMBER() OVER(ORDER BY PhSequence) AS RowNo, * FROM #testPhones) AS A WHERE RowNo = 1) AS A
    LEFT OUTER JOIN (SELECT PhNumber, PhSequence FROM (SELECT ROW_NUMBER() OVER(ORDER BY PhSequence) AS RowNo, * FROM #testPhones) AS B WHERE RowNo = 2) AS B ON 1=1
    LEFT OUTER JOIN (SELECT PhNumber, PhSequence FROM (SELECT ROW_NUMBER() OVER(ORDER BY PhSequence) AS RowNo, * FROM #testPhones) AS C WHERE RowNo = 3) AS C ON 1=1
    LEFT OUTER JOIN (SELECT PhNumber, PhSequence FROM (SELECT ROW_NUMBER() OVER(ORDER BY PhSequence) AS RowNo, * FROM #testPhones) AS D WHERE RowNo = 4) AS D ON 1=1

DROP TABLE #testPhones

This outputs the two following tables:

Output Tables

Thomas Erik Olsen
  • 71
  • 6