How to get complex values as columns

Question

I have three tables:

contacts
contact_phones
phone_types

Each contact from table contacts has multiple phone numbers in table contact_phones, and each phone number has a type_id from table phone_types (mobile, office, home etc.)

I need a query to bring me one row for each contact and a column for each phone type.

For each phone type column I need a comma seperated string of all the phone numbes this contact have from the table contact_phones.

For example:

first_name  last_name   Mobile_Phones           Office_Phones   
----------  ---------   ----------------------  -------------
Amancio     Ortega      0501111111,0502222222   031111111,032222222         
Avi         Zohar       0503333333              033333333
Beat        Hirt        0504444444,0505555555   NULL
Ben         Gurion      0501234567,05076545321  034444444,035555555
Dany        Azriel      0506764879,05065587436  034847968

Can it be done ?

I read it, buit this is not the same case. I need to have a column for each phone type and under each phnoe type column I need to concate all the contact phone numbers. I think I need to use pivot here... — Liran Friedman, Feb 27 '14 at 15:36
In your case you will need to add that `phone_type` column to the `GROUP BY` — Mahmoud Gamal, Feb 27 '14 at 15:40
But I need the phone type to be a column, not a row. I edited the question to be more clear. — Liran Friedman, Feb 27 '14 at 15:41

score 0 · Answer 1 · edited May 23 '17 at 12:10

0

You're looking to pivot data. There are several solutions, in increasing level of complexity (as far as number of moving parts goes):

Use PIVOT queries. For this you'll have to know all the columns up front;
Combine PIVOT with Dynamic SQL, see for example this question;
Keep data from your query unpivoted and use SSRS and create a matrix;

edited May 23 '17 at 12:10

Community

1
1

answered Feb 27 '14 at 15:46

Jeroen

60,696
40
206
339

I don't realy know how to do this. Can you please help me out and give me a sample query ? (-: – Liran Friedman Feb 27 '14 at 16:00
The documentation and examples is all there in linked articles. You should really try it first yourself, include your attempted solution in the question and be specific about the problem you're having. – Jeroen Feb 27 '14 at 16:02

score 0 · Accepted Answer · answered Feb 27 '14 at 16:02

This should get you started:

DECLARE @CONTACTS TABLE (ID INT IDENTITY(1,1), FNAME VARCHAR(100), LNAME VARCHAR(100))
DECLARE @CONTACTS_PHONE TABLE (ID INT IDENTITY(1,1), CONTACTID INT, TYPEID INT, PHONE VARCHAR(10))
DECLARE @PHONETYPE TABLE (ID INT IDENTITY(1,1), PHONETYPE VARCHAR(100))

INSERT INTO @CONTACTS (FNAME,LNAME) 
SELECT 'JIGGS', 'JEDI'

INSERT INTO @PHONETYPE (PHONETYPE)
SELECT 'MOBILE' UNION
SELECT 'HOME' UNION
SELECT 'OFFICE'

INSERT INTO @CONTACTS_PHONE (CONTACTID, TYPEID, PHONE)
SELECT 1, 1, '5555555' UNION
SELECT 1, 1, '6666666' UNION
SELECT 1, 2, '0000000' UNION
SELECT 1, 2, '1111111' UNION
SELECT 1, 3, '4444444'

;WITH PIVOTSTUFF
AS
(
SELECT *
FROM (SELECT DISTINCT T.CONTACTID, PT.PHONETYPE, 
               PHONES = REPLACE(STUFF( 
                (   SELECT ',' + PHONE AS [data()] 
                    FROM @CONTACTS_PHONE AS X
                    WHERE X.CONTACTID = T.CONTACTID 
                    AND X.TYPEID = T.TYPEID
                    ORDER BY PHONE 
                    FOR XML PATH ('') ), 1, 1, ''), ' ,', ',')
        FROM @CONTACTS_PHONE AS T
        JOIN @PHONETYPE PT
           ON T.TYPEID=PT.ID) DATATABLE
PIVOT(MIN([PHONES])
      FOR PHONETYPE IN ([HOME],[MOBILE],[OFFICE])) PIVOTTABLE
)

SELECT C.ID, C.FNAME, C.LNAME, T.HOME, T.MOBILE, T.OFFICE
FROM PIVOTSTUFF T
JOIN @CONTACTS C
   ON T.CONTACTID=C.ID

Note that `for xml path` will encode some characters, so for example if the phone column had a '&' in it, it will be replaced with a '&' — John Gibb, Feb 27 '14 at 16:20
Thank you, thank you, thank you (-: The pivot query did the job. Did you use the temp table for performance ? Or should I use select from the original tables ? And Can't I use a select so that it will support all phone types that can be added in the future ? — Liran Friedman, Mar 02 '14 at 08:55
I used the variable table just because I don't have tables that match -- substitute your own tables here. You can use dynamic sql to support new/variable columns. I can't explain it any better than this thread already did -- http://stackoverflow.com/questions/2922797/t-sql-pivot-possibility-of-creating-table-columns-from-row-values Good luck!! — Dave C, Mar 02 '14 at 14:45

AHiggins · Answer 3 · 2014-02-27T16:16:57.080

EDIT: Your original post made it sound as if you were trying to retrieve multiple columns from a comma separated list, not the other way around. The code below was intended to reverse that process.

=================================================================================

I modified some code I had for a similar problem: let me know if this helps. As noted in the comments, this requires a table called Numbers: I put it as a temp table and loaded it in my script below, but you'd be better off making it permanent and loading it once.

-- Test Data
CREATE TABLE Contacts 
  (
    Contacts_ID INT IDENTITY(1,1) NOT NULL,
    First_Name VARCHAR(100) NOT NULL,
    Last_Name VARCHAR(100) NOT NULL,
    Mobile_Phones VARCHAR(100) NULL,
    Office_Phones VARCHAR(100) NULL
  )

INSERT INTO Contacts (First_Name, Last_Name, Mobile_Phones, Office_Phones)
SELECT 'Amancio',     'Ortega',      '0501111111,0502222222',   '031111111,032222222'       UNION
SELECT 'Avi',         'Zohar',       '0503333333',              '033333333'                 UNION
SELECT 'Beat',        'Hirt',        '0504444444,0505555555',   NULL                        UNION
SELECT 'Ben',         'Gurion',      '0501234567,05076545321',  '034444444,035555555'       UNION
SELECT 'Dany',        'Azriel',      '0506764879,05065587436',  '034847968'             


-- Numbers table (for use in the formula): I would recommend creating a permanent table     and populating it once.  Make sure your count is higher than the length of your phone number     columns.
CREATE TABLE #Numbers (Number INT NOT NULL)
INSERT INTO #Numbers (Number) SELECT 1

DECLARE @Limit INT
DECLARE @Counter INT
SET @Limit = 101
SET @Counter = (SELECT MAX(Number) FROM #Numbers)

WHILE @Counter < @Limit
  BEGIN
    INSERT INTO #Numbers (Number) SELECT MAX(Number) + 1 FROM #Numbers
    SET @Counter = (SELECT MAX(Number) FROM #Numbers)
  END

--SELECT * FROM #Numbers

-- Retrieve the information
SELECT 
    Contacts_ID,
    First_Name,
    Last_Name,
    PhoneType,
    CAST(LTRIM(RTRIM(NULLIF(SUBSTRING(',' + PhoneNumber + ',' , Number , CHARINDEX(',' ,     ',' + PhoneNumber + ',' , Number) - Number) , ''))) AS VARCHAR(10)) PhoneNumber
FROM #Numbers,
    (SELECT 
        Contacts.Contacts_ID, 
        Contacts.First_Name,
        Contacts.Last_Name,
        Contacts.Mobile_Phones,
        Contacts.Office_Phones
    FROM Contacts
    ) P
UNPIVOT
    (PhoneNumber FOR PhoneType IN 
    (
        Mobile_Phones,  Office_Phones
    )) AS UP
WHERE 
    Number <= Len(',' + PhoneNumber + ',') AND SubString(',' + PhoneNumber + ',' ,     Number - 1, 1) = ',' AND 
    CharIndex(',' , ',' + PhoneNumber+ ',' , Number) - Number > 0


-- Clean up
/*
DROP TABLE Contacts
DROP TABLE #Numbers
*/

How to get complex values as columns

3 Answers3

Linked