How to join multiple tables , one table as dynamic columns and the other table as the values of these columns

Question

I'm new to SQL Server pivot, and i'm trying to solve a problem where i need to output the following tables into one table that includes the values based on one table's columns

Here's my tables

ContactGroup

Title          ID
---------- -----------
Group A         1

ContactsInGroups

ContactId   GroupId
----------- -----------
1           1
2           1
3           1

ContactVariables

ID          Name       GroupId     Order
----------- ---------- ----------- ------
1           Invoice    1           1
2           Due Date   1           1

ContactsVariablesValues

ContactVariableId ContactId   Value
----------------- ----------- -----
1                 1           600

Desired output

GroupId     ContactId     Invoice     Due Date 
----------- ----------- ----------- -----------
1           1           600           NULL     
1           2           NULL          NULL     
1           3           NULL          NULL

Due date is in contactvariable table i think you need to use dynamic pivot query — Sanpas, Jan 14 '19 at 20:15
Yes but i think they have a dynamic model and the due date wasn’t set for they contact . I think dynamic left join pivot can be work like excepted. — Sanpas, Jan 14 '19 at 20:18
@RyanWilson Due Date is the value for the column name in the table ContactVariables, and order is not an id , its a column to specify the order of the column on the output — Ali Kleit, Jan 14 '19 at 20:20
i think you have to create stored procedures with cursor, dynamic query to get your excepted output — Sanpas, Jan 14 '19 at 20:22
@pascalsanchez i'm not familiar with the pivot left join, i'll see what i can find. i saw some queries that uses pivot but the pivot also uses aggregate functions , my problem is that i need to output the value with respect to the columns that can be generated by a cursor or hard coded . — Ali Kleit, Jan 14 '19 at 20:28
you can using stored procedure on your program ? Because for my point of view it’s not possible to create dynamic query without cursor and i think hard coded doesn’t respond to your excepted at you want — Sanpas, Jan 14 '19 at 20:30
Find on stack over flow you can try thoses example : https://stackoverflow.com/q/10404348/9940803 — Sanpas, Jan 14 '19 at 20:46

critical_error · Accepted Answer · 2019-01-14T22:13:42.877

Ali, here is an example that will at least get you started. You can run the following in SSMS.

Create some table variables and insert your sample data.

DECLARE @ContactGroup TABLE ( id INT, title VARCHAR(50) );
INSERT INTO @ContactGroup ( id, title ) VALUES ( 1, 'Group A' );

DECLARE @ContactsInGroup TABLE ( ContactID INT, GroupID INT );
INSERT INTO @ContactsInGroup ( ContactID, GroupID ) VALUES ( 1, 1 ), ( 2, 1 ), ( 3, 1 );

DECLARE @ContactVariables TABLE ( id INT, [name] VARCHAR(50), GroupID INT, [Order] INT );
INSERT INTO @ContactVariables ( id, [name], GroupID, [Order] ) VALUES ( 1, 'Invoice', 1, 1 ), ( 2, 'Due Date', 1, 1 );

DECLARE @ContactsVariablesValues TABLE ( ContactVariableID INT, ContactID INT, [value] INT );
INSERT INTO @ContactsVariablesValues ( ContactVariableID, ContactID, [value] ) VALUES ( 1, 1, 600 );

Then query the data as follows:

SELECT
    ContactGroup.id AS GroupID
    , ContactsInGroup.ContactID
    , ContactVars.Invoice
    , ContactVars.[Due Date]
FROM @ContactGroup AS ContactGroup
INNER JOIN @ContactsInGroup AS ContactsInGroup
    ON ContactGroup.id = ContactsInGroup.GroupID
OUTER APPLY (

    SELECT 
        [Invoice], [Due Date]
    FROM (
        SELECT
            Vars.[name]
            , Vals.[value]
        FROM @ContactVariables AS Vars
        LEFT OUTER JOIN @ContactsVariablesValues Vals
            ON Vars.id = Vals.ContactVariableID
        WHERE
            Vars.GroupID = 1
            AND Vals.ContactID = ContactsInGroup.ContactID

    ) AS ContactData
    PIVOT (
        MIN( [value] )
        FOR [name] IN ( 
            [Invoice], [Due Date] 
        )
    ) AS pvt

) AS ContactVars
ORDER BY
    ContactGroup.id, ContactsInGroup.ContactID;

Which returns:

+---------+-----------+---------+----------+
| GroupID | ContactID | Invoice | Due Date |
+---------+-----------+---------+----------+
|       1 |         1 | 600     | NULL     |
|       1 |         2 | NULL    | NULL     |
|       1 |         3 | NULL    | NULL     |
+---------+-----------+---------+----------+

Things to note

The "magic" here is in the OUTER APPLY. This allows us to query a subset of data based on the primary data returned, in this case the GroupID and ContactID. OUTER APPLY will also return rows with NULL values like you desire.

You're going to have some challenges here, namely that to use a PIVOT as shown in my example, you will need to know all the values ( Invoice, Due Date, etc... ) that will become column headers. Based on your setup, I'm thinking this may not be the case, so you will be forced to resort to an technique that creates and executes a dynamic PIVOT statement for you within the OUTER APPLY.

You also might consider using a TABLE VALUED FUNCTION that does the PIVOT work that can then be JOINed on vs. an OUTER APPLY.

You have several options, but hopefully this helps jumpstart your thinking.

Nicely explained, now I'll work on getting the columns dynamically (Invoice, due date) with the above query — Ali Kleit, Jan 15 '19 at 20:53

How to join multiple tables , one table as dynamic columns and the other table as the values of these columns

1 Answers1