How I can convert Rows to Columns in SQL?

Question

I have a table like this: Phones

  ------------------------------------------------------
   | CustomerID           | PhoneID      | PhoneNum     |
   -----------------------------------------------------
   | 1                    | 101          | 09811111     |
   | 1                    | 102          | 09822222     |
   | 1                    | 103          | 09833333     |
   | 2                    | 104          | 09844444     |
   | 2                    | 105          | 09855555     |
   -------------------------------------------------

I want query that give me bellow result:

--------------------------------------------------------------------------
| CustomerID           | PhoneNum1    |   PhoneNum2    |   PhoneNum3      |
--------------------------------------------------------------------------
| 1                    | 09811111     |   09822222     |   09833333       |
| 2                    | 09844444     |   09855555     |     NULL         |           
---------------------------------------------------------------------------

How can I build the result?

You're looking to PIVOT data example https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — Richard, Oct 11 '19 at 09:50
Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Richard, Oct 11 '19 at 09:51

score 3 · Accepted Answer · answered Oct 11 '19 at 09:49

3

We can handle this requirement with the help of ROW_NUMBER and a pivot query:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY PhoneID) rn
    FROM Phones
)

SELECT
    CustomerID,
    MAX(CASE WHEN rn = 1 THEN PhoneNum END) AS PhoneNum1,
    MAX(CASE WHEN rn = 2 THEN PhoneNum END) AS PhoneNum2,
    MAX(CASE WHEN rn = 3 THEN PhoneNum END) AS PhoneNum3
FROM cte
GROUP BY
    CustomerID
ORDER BY
    CustomerID;

Demo

answered Oct 11 '19 at 09:49

Tim Biegeleisen

502,043
27
286
360

Thank you very much. Another question, Do I can inner join this query to another table? – Ali Moosavi Oct 11 '19 at 10:21
@AliMoosavi Yes, this should be possible, but you would probably do that join inside the CTE. In any case, if you have another query in mind you might want to open a new question. – Tim Biegeleisen Oct 11 '19 at 10:28
If I want join this cte with Customer table include CustomerID,Name,Family and ... , how can I do that? – Ali Moosavi Oct 11 '19 at 17:23

score 0 · Answer 2 · answered Oct 12 '19 at 07:39


The query above was very useful. But when I use the Where, the result is not right


WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY PhoneID) rn
    FROM Phones
)

SELECT
    CustomerID,
    MAX(CASE WHEN rn = 1 THEN PhoneNum END) AS PhoneNum1,
    MAX(CASE WHEN rn = 2 THEN PhoneNum END) AS PhoneNum2,
    MAX(CASE WHEN rn = 3 THEN PhoneNum END) AS PhoneNum3
FROM cte
where PhoneNum ='09811111'
GROUP BY
    CustomerID
ORDER BY
    CustomerID;

Result:
--------------------------------------------------------------------------
| CustomerID           | PhoneNum1    |   PhoneNum2    |   PhoneNum3      |
--------------------------------------------------------------------------
| 1                    | 09811111     |   NULL         |   NULL           |
---------------------------------------------------------------------------

score 0 · Answer 3 · answered Oct 12 '19 at 11:53


I find a way for my asked

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY PhoneID) rn
    FROM Phones
)

SELECT
    CustomerID,
    MAX(CASE WHEN rn = 1 THEN PhoneNum END) AS PhoneNum1,
    MAX(CASE WHEN rn = 2 THEN PhoneNum END) AS PhoneNum2,
    MAX(CASE WHEN rn = 3 THEN PhoneNum END) AS PhoneNum3
FROM cte
where CustomerID = ( select CustomerID from cte where PhoneNum ='09811111' )
GROUP BY
    CustomerID
ORDER BY
    CustomerID;

How I can convert Rows to Columns in SQL?

3 Answers3

Demo