SQL merging tables

Question

I am trying to merge a few tables in order to get the output as outlined in the image below.

enter image description here

My issue is that I am not sure what type of joins to use to achieve that

Can someone please help me with the syntax.

You might need to use a [`PIVOT`](http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query) — valverij, Jul 17 '14 at 12:23
how many tests vs how many users, logically i would imagine you want to switch the rows and columns of your output. What are you using to output this report? — Daniel E., Jul 17 '14 at 12:31
Please, post the data for your question in usable format. Ideally, F5-ready `create table` and `insert` statements. For now I'll only give you a hint: `PIVOT` and `FULL OUTER JOIN`. — AdamL, Jul 17 '14 at 12:53

score 3 · Accepted Answer · answered Jul 17 '14 at 13:19

You could do something like this, it's a dynamic pivot as you might add/ take away users?

CREATE TABLE #Tests (
    Test_ID INT,
    TestName VARCHAR(50));
INSERT INTO #Tests VALUES (1, 'SQL Test');
INSERT INTO #Tests VALUES (2, 'C# Test');
INSERT INTO #Tests VALUES (3, 'Java Test');
CREATE TABLE #Users (
    [User_ID] INT,
    UserName VARCHAR(50));
INSERT INTO #Users VALUES (1, 'Joe');
INSERT INTO #Users VALUES (2, 'Jack');
INSERT INTO #Users VALUES (3, 'Jane');
CREATE TABLE #UserTests (
    ID INT,
    [User_ID] INT,
    Test_ID INT,
    Completed INT);
INSERT INTO #UserTests VALUES (1, 1, 1, 0);
INSERT INTO #UserTests VALUES (2, 1, 2, 1);
INSERT INTO #UserTests VALUES (3, 1, 3, 1);
INSERT INTO #UserTests VALUES (4, 2, 1, 0);
INSERT INTO #UserTests VALUES (5, 2, 2, 0);
INSERT INTO #UserTests VALUES (6, 2, 3, 0);
INSERT INTO #UserTests VALUES (7, 3, 1, 1);
INSERT INTO #UserTests VALUES (8, 3, 2, 1);
INSERT INTO #UserTests VALUES (9, 3, 3, 1);
DECLARE @Cols VARCHAR(MAX);
SELECT @Cols = STUFF((SELECT distinct ',' + QUOTENAME(u.UserName) 
            FROM #Users u
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');
DECLARE @Query NVARCHAR(MAX);
SELECT @Query = 'SELECT TestName, ' + @Cols + ' FROM
(
    SELECT 
        t.TestName,
        u.UserName,
        ut.Completed
FROM
    #Tests t
    INNER JOIN #UserTests ut ON ut.Test_ID = t.Test_ID
    INNER JOIN #Users u ON u.[User_ID] = ut.[User_ID]) x
    PIVOT (
        MAX(Completed)
        FOR UserName IN (' + @Cols + ')
    ) AS pt';
EXEC(@Query);

Results are:

TestName    Jack    Jane    Joe
C# Test     0       1       1
Java Test   0       1       1
SQL Test    0       1       0

(Same results as yours, but in a different sort order.)

Yes users might be added/removed. I will try the above. thanks — sd_dracula, Jul 17 '14 at 16:31

SQL merging tables

1 Answers1