locked
Adding results of a query to another query via dynamically added columns. RRS feed

  • Question

  • Hi all,

    For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row.

    How can I achieve that?

    I want my output to be

    CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc

    Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.

    I want to do it using SELECT. Is it possible?

    Harley.

    Thursday, July 30, 2015 5:26 PM

Answers

  • Try:

    ;with cte as (select P.Id, P.First_Name, P.Last_Name, 'Tel' + CAST(ROW_NUMBER() over (partition by P.Id order by Ph.Id) as varchar(10)) as Rn, Ph.Phone

    from PersonData P LEFT JOIN Phones Ph ON P.Id = Ph.PersonID)

    select * from cte PIVOT (max(Phone) FOR Rn IN ([Tel1],[Tel2],[Tel3])) pvt -- 3 phones, you can use more


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Eric__Zhang Monday, August 10, 2015 12:11 AM
    Thursday, July 30, 2015 6:08 PM
  • Give this a try...

    IF OBJECT_ID('tempdb..#Customer') IS NOT NULL
    DROP TABLE #Customer;
    
    IF OBJECT_ID('tempdb..#Phones') IS NOT NULL
    DROP TABLE #Phones;
    
    CREATE TABLE #Customer (
    	CustomerID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    	FirstName VARCHAR(15) NOT NULL,
    	LastName VARCHAR(15) NOT NULL 
    	);
    
    CREATE TABLE #Phones (
    	CustomerID INT NOT NULL,
    	PhoneType VARCHAR(10),
    	PhoneNumber VARCHAR(20),
    	PRIMARY KEY (CustomerID, PhoneType)
    	);
    
    INSERT #Customer (FirstName,LastName) VALUES
    	('Joe','Blow'), ('Mike','Brown'), ('John Q','Public'), ('Alan','Smithee'), ('Bob','Jones');
    
    INSERT #Phones (CustomerID,PhoneType,PhoneNumber) VALUES
    	(1,'Home','555-321-1234'), (1,'Cell','555-333-4444'), (1,'Fax','555-444-5555'),
    	(2,'Home','444-321-1234'), (2,'Cell','444-333-4444'), (2,'Office','444-444-5555'),
    	(3,'Home','333-321-1234'), (3,'Cell','333-333-4444'), (3,'Other','333-444-5555'),
    	(4,'Cell','222-321-1234'), (4,'Office','222-333-4444'), (4,'Fax','222-444-5555'),
    	(5,'Home','666-321-1234'), (5,'Other','666-333-4444'), (5,'Fax','666-444-5555')
    
    SELECT 
    	c.CustomerID,
    	c.FirstName,
    	c.LastName,
    	MAX(CASE WHEN p.PhoneType = 'Home' THEN p.PhoneNumber ELSE '' END) AS HomePhone,
    	MAX(CASE WHEN p.PhoneType = 'Cell' THEN p.PhoneNumber ELSE '' END) AS CellPhone,
    	MAX(CASE WHEN p.PhoneType = 'Office' THEN p.PhoneNumber ELSE '' END) AS OfficePhone,
    	MAX(CASE WHEN p.PhoneType = 'Fax' THEN p.PhoneNumber ELSE '' END) AS FaxPhone,
    	MAX(CASE WHEN p.PhoneType = 'Other' THEN p.PhoneNumber ELSE '' END) AS OtherPhone
    FROM 
    	#Customer c
    	JOIN #Phones p
    		ON c.CustomerID = p.CustomerID
    GROUP BY 
    	c.CustomerID,
    	c.FirstName,
    	c.LastName

    Results...

    CustomerID  FirstName       LastName        HomePhone            CellPhone            OfficePhone          FaxPhone             OtherPhone
    ----------- --------------- --------------- -------------------- -------------------- -------------------- -------------------- --------------------
    1           Joe             Blow            555-321-1234         555-333-4444                              555-444-5555         
    2           Mike            Brown           444-321-1234         444-333-4444         444-444-5555                              
    3           John Q          Public          333-321-1234         333-333-4444                                                   333-444-5555
    4           Alan            Smithee                              222-321-1234         222-333-4444         222-444-5555         
    5           Bob             Jones           666-321-1234                                                   666-444-5555         666-333-4444


    Jason Long

    • Proposed as answer by Naomi N Friday, July 31, 2015 1:58 AM
    • Marked as answer by Eric__Zhang Monday, August 10, 2015 12:11 AM
    Thursday, July 30, 2015 10:46 PM

All replies

  • Give us some example data and DDL, and I'll take a look.

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    Thursday, July 30, 2015 5:32 PM
  • Try:

    ;with cte as (select P.Id, P.First_Name, P.Last_Name, 'Tel' + CAST(ROW_NUMBER() over (partition by P.Id order by Ph.Id) as varchar(10)) as Rn, Ph.Phone

    from PersonData P LEFT JOIN Phones Ph ON P.Id = Ph.PersonID)

    select * from cte PIVOT (max(Phone) FOR Rn IN ([Tel1],[Tel2],[Tel3])) pvt -- 3 phones, you can use more


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Eric__Zhang Monday, August 10, 2015 12:11 AM
    Thursday, July 30, 2015 6:08 PM
  • Give this a try...

    IF OBJECT_ID('tempdb..#Customer') IS NOT NULL
    DROP TABLE #Customer;
    
    IF OBJECT_ID('tempdb..#Phones') IS NOT NULL
    DROP TABLE #Phones;
    
    CREATE TABLE #Customer (
    	CustomerID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    	FirstName VARCHAR(15) NOT NULL,
    	LastName VARCHAR(15) NOT NULL 
    	);
    
    CREATE TABLE #Phones (
    	CustomerID INT NOT NULL,
    	PhoneType VARCHAR(10),
    	PhoneNumber VARCHAR(20),
    	PRIMARY KEY (CustomerID, PhoneType)
    	);
    
    INSERT #Customer (FirstName,LastName) VALUES
    	('Joe','Blow'), ('Mike','Brown'), ('John Q','Public'), ('Alan','Smithee'), ('Bob','Jones');
    
    INSERT #Phones (CustomerID,PhoneType,PhoneNumber) VALUES
    	(1,'Home','555-321-1234'), (1,'Cell','555-333-4444'), (1,'Fax','555-444-5555'),
    	(2,'Home','444-321-1234'), (2,'Cell','444-333-4444'), (2,'Office','444-444-5555'),
    	(3,'Home','333-321-1234'), (3,'Cell','333-333-4444'), (3,'Other','333-444-5555'),
    	(4,'Cell','222-321-1234'), (4,'Office','222-333-4444'), (4,'Fax','222-444-5555'),
    	(5,'Home','666-321-1234'), (5,'Other','666-333-4444'), (5,'Fax','666-444-5555')
    
    SELECT 
    	c.CustomerID,
    	c.FirstName,
    	c.LastName,
    	MAX(CASE WHEN p.PhoneType = 'Home' THEN p.PhoneNumber ELSE '' END) AS HomePhone,
    	MAX(CASE WHEN p.PhoneType = 'Cell' THEN p.PhoneNumber ELSE '' END) AS CellPhone,
    	MAX(CASE WHEN p.PhoneType = 'Office' THEN p.PhoneNumber ELSE '' END) AS OfficePhone,
    	MAX(CASE WHEN p.PhoneType = 'Fax' THEN p.PhoneNumber ELSE '' END) AS FaxPhone,
    	MAX(CASE WHEN p.PhoneType = 'Other' THEN p.PhoneNumber ELSE '' END) AS OtherPhone
    FROM 
    	#Customer c
    	JOIN #Phones p
    		ON c.CustomerID = p.CustomerID
    GROUP BY 
    	c.CustomerID,
    	c.FirstName,
    	c.LastName

    Results...

    CustomerID  FirstName       LastName        HomePhone            CellPhone            OfficePhone          FaxPhone             OtherPhone
    ----------- --------------- --------------- -------------------- -------------------- -------------------- -------------------- --------------------
    1           Joe             Blow            555-321-1234         555-333-4444                              555-444-5555         
    2           Mike            Brown           444-321-1234         444-333-4444         444-444-5555                              
    3           John Q          Public          333-321-1234         333-333-4444                                                   333-444-5555
    4           Alan            Smithee                              222-321-1234         222-333-4444         222-444-5555         
    5           Bob             Jones           666-321-1234                                                   666-444-5555         666-333-4444


    Jason Long

    • Proposed as answer by Naomi N Friday, July 31, 2015 1:58 AM
    • Marked as answer by Eric__Zhang Monday, August 10, 2015 12:11 AM
    Thursday, July 30, 2015 10:46 PM
  • The pivot would work rather well probably better than a group by solution below :)

    The only thing i wanted to add is that with both of the solutions you have to define the total number of possible tel number columns. so normally you would want to provision enough numbers so you don't run out of columns to store your data

    If you really must have a true dynamic solution  and cant have columns that are null if there are no numbers to store you are going back to looping/cursoring and so on which is not optimal. 


    Friday, July 31, 2015 12:40 AM
  • The pivot would work rather well probably better than a group by solution below :)

    The only thing i wanted to add is that with both of the solutions you have to define the total number of possible tel number columns. so normally you would want to provision enough numbers so you don't run out of columns to store your data

    If you really must have a true dynamic solution  and cant have columns that are null if there are no numbers to store you are going back to looping/cursoring and so on which is not optimal. 


    The PIVOT operator is (IMO) utterly useless... It offer no performance benefits over aggregated case expressions. It simply offers a less intuitive syntax and WAY less flexibility.

    No matter what method you use, dynamic pivots can be achieved by using dynamic sql... Without the use of cursors or loops. The problem is that you can't really do anything with dynamic pivots in sql (except look at the results in SSMS). I can't think of a single reporting platform that will play nicely with an ever changing set of input columns. So, unless the code is for a custom application that's designed to accept whatever column are being thrown at it at any given time, dynamic pivots should be left to the front end software. SSRS Matrix tablix for example.


    Jason Long

    Friday, July 31, 2015 1:18 AM
  • Totally agreed on:

    "dynamic pivots should be left to the front end software."

    and i always wished that MS would provide more flexibility with their PIVOT functionality. 

    Friday, July 31, 2015 1:28 AM
  • The PIVOT operator is (IMO) utterly useless... It offer no performance benefits over aggregated case expressions. It simply offers a less intuitive syntax and WAY less flexibility.

    Hear, hear!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, July 31, 2015 7:25 AM
  • I never said that PIVOT is going to perform better than GROUP BY and Aggrigated CASE expression. I said the solution is better. And since everyone is agains PIVOT functionality, someone has to take its side...

    Jason, 

    You are making some assumptions about the data that you are working with, basically you are assuming that there can only be one Cell ane Home and so on number, what if there are multiples? obviously you can fix it by modifying the table definetion by desegnating Cell1 Cell2 and so on...but what if you cant modify an application to support it?

    Also even if you are addressing multiple cell, home,work,fax and so on numbers your table could get pretty wide and in a lot of cased may contain nulls since you have to address the extrime cases.

    Pivot just cares about total maximum number of telephone numbers and dont care about maintaining types of phones.

    Allow me to demonstrate: i modified your code slightly to remove the primary key constraint on #phones tables and also added another Cell number for customerID 3. I also modified Naomi's code to work with your tables and made it match total possible telephone numbers to 5 so its the same as yours.

    Code below:

    IF OBJECT_ID('tempdb..#Customer') IS NOT NULL
    DROP TABLE #Customer;

    IF OBJECT_ID('tempdb..#Phones') IS NOT NULL
    DROP TABLE #Phones;

    CREATE TABLE #Customer (
    CustomerID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    FirstName VARCHAR(15) NOT NULL,
    LastName VARCHAR(15) NOT NULL 
    );

    CREATE TABLE #Phones (
    CustomerID INT NOT NULL,
    PhoneType VARCHAR(10),
    PhoneNumber VARCHAR(20),
    --PRIMARY KEY (CustomerID, PhoneType)
    );

    INSERT #Customer (FirstName,LastName) VALUES
    ('Joe','Blow'), ('Mike','Brown'), ('John Q','Public'), ('Alan','Smithee'), ('Bob','Jones');

    INSERT #Phones (CustomerID,PhoneType,PhoneNumber) VALUES
    (1,'Home','555-321-1234'), (1,'Cell','555-333-4444'), (1,'Fax','555-444-5555'),
    (2,'Home','444-321-1234'), (2,'Cell','444-333-4444'), (2,'Office','444-444-5555'),
    (3,'Home','333-321-1234'), (3,'Cell','333-333-4444'), (3,'Other','333-444-5555'),
    (3,'Cell','555-5555'),
    (4,'Cell','222-321-1234'), (4,'Office','222-333-4444'), (4,'Fax','222-444-5555'),
    (5,'Home','666-321-1234'), (5,'Other','666-333-4444'), (5,'Fax','666-444-5555')

    SELECT 
    c.CustomerID,
    c.FirstName,
    c.LastName,
    MAX(CASE WHEN p.PhoneType = 'Home' THEN p.PhoneNumber ELSE '' END) AS HomePhone,
    MAX(CASE WHEN p.PhoneType = 'Cell' THEN p.PhoneNumber ELSE '' END) AS CellPhone,
    MAX(CASE WHEN p.PhoneType = 'Office' THEN p.PhoneNumber ELSE '' END) AS OfficePhone,
    MAX(CASE WHEN p.PhoneType = 'Fax' THEN p.PhoneNumber ELSE '' END) AS FaxPhone,
    MAX(CASE WHEN p.PhoneType = 'Other' THEN p.PhoneNumber ELSE '' END) AS OtherPhone
    FROM 
    #Customer c
    JOIN #Phones p
    ON c.CustomerID = p.CustomerID
    GROUP BY 
    c.CustomerID,
    c.FirstName,
    c.LastName

    ;with cte as 
    (select P.CustomerID, P.FirstName, P.LastName, 'Tel' + CAST(ROW_NUMBER() over (partition by P.CustomerID order by Ph.CustomerID) as varchar(10)) as Rn, 
     'PhoneType:'+ph.PhoneType+' Number:'+Ph.PhoneNumber  AS PhoneNumber
    from #Customer P LEFT JOIN #Phones Ph ON P.CustomerID = Ph.CustomerID)


    select * from cte PIVOT (max(PhoneNumber) FOR Rn IN ([Tel1],[Tel2],[Tel3],[Tel4],[Tel5])) pvt 

    Result Set from your code:

    


    Notice that John Q only has 3 numbers in your result set:

    Result Set from Naomi' code:


    Pivot picks up the second cell number with no issues for customer id 3...so in this case pivot is actually more flexable than group by MAX(CASE) and will prodice the correct results with the same table width, to accomodate the same functionality with yours you would have to add Cell2 Column to your result set.





    • Edited by SQLGru Friday, July 31, 2015 2:51 PM
    Friday, July 31, 2015 2:43 PM
  • IMHO, PIVOT just provides a more compact way to achieve the same functionality. I like PIVOT syntax as it's less verbose. In case you need to pivot by one column only, I'd pick the PIVOT syntax. If you need to pivot on multiple columns, then there is no other choice as the aggregates.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, July 31, 2015 2:51 PM
  • Still not a problem for aggregated case expressions... 

    IF OBJECT_ID('tempdb..#Customer') IS NOT NULL
    DROP TABLE #Customer;
    
    IF OBJECT_ID('tempdb..#Phones') IS NOT NULL
    DROP TABLE #Phones;
    
    CREATE TABLE #Customer (
    CustomerID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    FirstName VARCHAR(15) NOT NULL,
    LastName VARCHAR(15) NOT NULL 
    );
    
    CREATE TABLE #Phones (
    CustomerID INT NOT NULL,
    PhoneType VARCHAR(10),
    PhoneNumber VARCHAR(20),
    --PRIMARY KEY (CustomerID, PhoneType)
    );
    
    INSERT #Customer (FirstName,LastName) VALUES
    ('Joe','Blow'), ('Mike','Brown'), ('John Q','Public'), ('Alan','Smithee'), ('Bob','Jones');
    
    INSERT #Phones (CustomerID,PhoneType,PhoneNumber) VALUES
    (1,'Home','555-321-1234'), (1,'Cell','555-333-4444'), (1,'Fax','555-444-5555'),
    (2,'Home','444-321-1234'), (2,'Cell','444-333-4444'), (2,'Office','444-444-5555'),
    (3,'Home','333-321-1234'), (3,'Cell','333-333-4444'), (3,'Other','333-444-5555'),
    (3,'Cell','555-5555'),
    (4,'Cell','222-321-1234'), (4,'Office','222-333-4444'), (4,'Fax','222-444-5555'),
    (5,'Home','666-321-1234'), (5,'Other','666-333-4444'), (5,'Fax','666-444-5555');
    
    WITH PhoneRN AS (
    	SELECT 
    		p.CustomerID,
    		p.PhoneType,
    		p.PhoneNumber,
    		row_number() OVER (PARTITION BY p.CustomerID ORDER BY (SELECT NULL)) AS RN
    	FROM 
    		#Phones p
    )
    	SELECT 
    		c.CustomerID,
    		CONCAT(c.FirstName, ' ', c.LastName) AS Customer,
    		MAX(CASE WHEN pr.RN = 1 THEN CONCAT(pr.PhoneType, ': ', pr.PhoneNumber) END) AS Tel1,
    		MAX(CASE WHEN pr.RN = 2 THEN CONCAT(pr.PhoneType, ': ', pr.PhoneNumber) END) AS Tel2,
    		MAX(CASE WHEN pr.RN = 3 THEN CONCAT(pr.PhoneType, ': ', pr.PhoneNumber) END) AS Tel3,
    		MAX(CASE WHEN pr.RN = 4 THEN CONCAT(pr.PhoneType, ': ', pr.PhoneNumber) END) AS Tel4,
    		MAX(CASE WHEN pr.RN = 5 THEN CONCAT(pr.PhoneType, ': ', pr.PhoneNumber) END) AS Tel5
    	FROM 
    		#Customer c
    		LEFT JOIN PhoneRN pr
    			ON c.CustomerID = pr.CustomerID
    	GROUP BY 
    		c.CustomerID,
    		c.FirstName,
    		c.LastName

    Results...

    Outputting in different format isn't a problem... Just note that in both cases we're using a predetermined number of pivot columns. Sure, we can add "padding columns" to accommodate an exorbitant number of phone numbers but who wants to look at an output with a bunch of empty columns that are there "just in case"?

    I'm not as hard line as Celko on this topic... Yes, I agree with him with respect to the idea that pivoting is pure display functionality and "should" be done in the rendering application... BUT... We all live within the limitations of our tools, and in most cases (at least within my own personal experience), if the number of pivot columns is known and fixed at design time, it makes more sense to pivot the columns in SQL.

    I say that because 1) the aggregation that takes place tends to make for a smaller amount of data being sent across the network from the db server to the report server. 2) In SSRS I prefer working with table style tablixes more than the matrix style. 3) Our DB servers have far more resources and processing power than our report servers... The net result is that I get faster design times and faster render times doing the pivots in SQL...

    With that said... If the data doesn't lend itself to a fixed number of pivot columns, I'm not going to screw around with dynamic pivots or extra padding columns. I'm just going to let SSRS handle the pivot with a matrix on the report side... Because that's exactly what it's designed to do. If I have a hammer and a screwdriver, I'm not going to pound on screws just because I like my hammer better...

    As for my previous comments about the pivot operator... I prefaced them with (IMO)... Meaning that my comments were based 100% on my own opinion... And I stand by them. The fact is, I have yet to see anyone do anything with the pivot operator that I can't do with aggregated case expressions.

    Naomi likes them and uses them... Our opinions simply differ in that regard... That in no way diminishes the very high opinion I have of her, her coding abilities or the contributions she's made to the SQL server community as a whole.

    If you like using the pivot operator, by all means do so... 


    Jason Long

    Saturday, August 1, 2015 12:17 AM
  • Jason,

    The funny part is that i agree with you. I would rather use a group by than a pivot,  the only thing i was saying is that the original solution you proposed was not as flexible as pivot functionality.  That's all.

    Saturday, August 1, 2015 1:17 AM
  • No worries. It's always good to look at compare different solutions to a problem. The day may come when MS decides to add some secret sauce to the the PIVOT operator code, which makes it substantially faster than an equivalent aggregated case expression... If that day ever comes, my opinion will will change. :D

    Jason Long

    Saturday, August 1, 2015 1:37 AM