CODEWITHSUNDEEP
sqlsql-server
Zac Herman
Zac Herman

Reputation: 71

SQL Server: How can I group multiple row values into separate columns?

I am attempting to create a SELECT query that will allow me to combine multiple phone numbers for a Contact into separate columns.

For example, I would like the following 

Email  |  Phone  |  ID  |
------------------------|
[email protected]| 555-5555| 001  |
[email protected]| 555-5556| 001  |

to instead be:

Email  |  Phone  |  Phone2  | Phone3 |  ID  |
--------------------------------------------|
[email protected]| 555-5555| 555-5556 |  NULL  | 001  |

Although this may be bad practice, it is strictly so I can export the results to a CSV in order to upload that data into a CRM system which does not allow any duplicates when importing. I have looked all over and cannot seem to find an answer that deals with phone numbers since MIN and MAX will only allow for two and no more.

The most complicated part is how the database is structured and will most likely involve a complicated query. Here's what I tried so far but can only get 2 numbers and I need the remaining columns (please keep in mind, I'm not a SQL guy):

SELECT DISTINCT
       C.CONTACTID,
       MAX(T.NUMBERVALUE) AS Phone1,
       MIN(T.numbervalue) AS Phone2
FROM TBL_PHONE T
     JOIN TBL_CONTACT C ON C.CONTACTID = T.CONTACTID
     JOIN TBL_EMAIL E ON E.CONTACTID = C.CONTACTID
WHERE T.NUMBERVALUE IS NOT NULL
      AND LEN(T.NUMBERVALUE) > 0
GROUP BY C.CONTACTID;

Upvotes: 2

Views: 1962

Answers (1)

S3S
S3S

Reputation: 25112

Here is a dynamic pivot way:

declare @table table (Email varchar(64), Phone varchar(16), ID varchar(3))
insert into @table
values

('[email protected]','555-5555','001'),
('[email protected]','555-5556','001'),
('[email protected]','555-5557','001'),
('[email protected]','555-5558','001'),
('[email protected]','333-5556','002'),
('[email protected]','444-5556','002'),
('[email protected]','777-5556','002')


select
    Email
    ,Phone
    ,ID
    ,row_number() over (partition by ID order by Phone) as RN
into #staging
from 
    @table




DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(RN)
FROM (SELECT DISTINCT RN FROM #staging) AS RN

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT Email, ID, ' + @ColumnName + '
    FROM #staging
    PIVOT(MAX(Phone) 
          FOR RN IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

drop table #staging

If you are only expecting 3, as you stated, you can skip the dynamic...

declare @table table (Email varchar(64), Phone varchar(16), ID varchar(3))
insert into @table
values

('[email protected]','555-5555','001'),
('[email protected]','555-5556','001'),
('[email protected]','333-5556','002'),
('[email protected]','444-5556','002'),
('[email protected]','777-5556','002')

;with cte as(
select
    Email
    ,Phone
    ,ID
    ,row_number() over (partition by ID order by Phone) as RN
from 
    @table)

select
    Email
    ,max(case when RN = 1 then Phone end) as Phone1
    ,max(case when RN = 2 then Phone end) as Phone2
    ,max(case when RN = 3 then Phone end) as Phone3
    ,ID
from
    cte
group by
    Email
    ,ID

Upvotes: 5

Related Questions