Left Join Pivot Table with Column from different Table

Question

I have successfully pivoted a table with a query of this format:

Pivoted Table

SELECT returnColumn, ISNULL(rule1,0), ISNULL(rule2,0)
FROM   (SELECT DISTINCT returnColumn,
                        ruleColumn,
                        bool
        FROM   tableRules) AS st

PIVOT (AVG(bool) FOR ruleColumn IN (rule1,rule2) As pt

ORDER BY returnColumn

Edit

My pivoted table has the following format:

+--------------+---------------------+
| returnColumn | rule1  rule2  rule3 |
+--------------+---------------------+
| return1      |   0      1      0   |
| return2      |   1      0      1   |
+--------------+---------------------+

There is another table otherRuleTable with the columns returnColumn and rule4. It looks like this:

+--------------+-------+
| returnColumn | rule4 |
+--------------+-------+
| return1      |   1   |
| return2      |   1   |
+--------------+-------+

I would like this to happen:

+--------------+---------------------------+
| returnColumn | rule1  rule2  rule3  rule4|
+--------------+---------------------------+
| return1      |   0      1      0      1  |
| return2      |   1      0      1      1  |
+--------------+---------------------------+

Attempt

I think this will require a left join, but I am struggling with the syntax. I tried this:

(SELECT returnColumn, ISNULL(rule1,0), ISNULL(rule2,0)
FROM   (SELECT DISTINCT returnColumn,
                        ruleColumn,
                        bool
        FROM   tableRules) AS SourceTable

PIVOT (AVG(bool) FOR ruleColumn IN (rule1,rule2) As pt)
LEFT JOIN
(SELECT rule4 FROM otherRuleTable ort)
ON pt.returnColumn = ort.returnColumn

ORDER BY returnColumn

How can I accomplish this?

ANSWER

SQL server join tables and pivot

Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, Oct 04 '16 at 15:23

score 0 · Answer 1 · answered Oct 05 '16 at 13:41

There are two separate issues with your query. the one is plain syntax related and manifests in your first query already. The second one is the question of what join type to use. You don't provide enough information for the second question to be answered. However, you can find a detailed explanation about the different join types and when to use which in my A Join A Day article series.

As for the syntax error, let's look at the pivot query first. Here is some test data setup:

SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE dbo.tableRules(
   returnColumn VARCHAR(10),
   ruleColumn VARCHAR(10),
   bool INT
  );

CREATE TABLE dbo.otherRuleTable(
   returnColumn VARCHAR(10),
   rule4 INT
  );


 INSERT INTO dbo.tableRules(returnColumn, ruleColumn, bool)
 VALUES
   ('return1','rule1',0),
   ('return1','rule2',1),
   ('return1','rule3',0),
   ('return2','rule1',1),
   ('return2','rule2',1),
   ('return2','rule3',0);

 INSERT INTO dbo.otherRuleTable(returnColumn, rule4)
 VALUES
   ('return1',0),
   ('return2',1);

SQL Fiddle is currently broken, so the link might not work.

The simple pivot query looks like this:

SELECT returnColumn, ISNULL(rule1,0) AS rule1, ISNULL(rule2,0) AS rule2, ISNULL(rule3,0) AS rule3
  FROM 
  (
    SELECT DISTINCT returnColumn, ruleColumn, bool
      FROM dbo.tableRules
  ) AS SourceTable
 PIVOT 
 (
   AVG(bool) 
   FOR ruleColumn IN (rule1,rule2,rule3)
 ) As pt;

All that was off in your version was a superfluous parenthesis.

To prepare for the join, you need to turn this query into a sub-query:

SELECT *
  FROM
  (
    SELECT returnColumn, ISNULL(rule1,0) AS rule1, ISNULL(rule2,0) AS rule2, ISNULL(rule3,0) AS rule3
      FROM 
      (
        SELECT DISTINCT returnColumn, ruleColumn, bool
          FROM dbo.tableRules
      ) AS SourceTable
     PIVOT 
     (
       AVG(bool) 
       FOR ruleColumn IN (rule1,rule2,rule3)
     ) As pt
  )pt2;

with that in place, now you can join to it, as if it were a table:

      SELECT *
        FROM
        (
          SELECT returnColumn, ISNULL(rule1,0) AS rule1, ISNULL(rule2,0) AS rule2, ISNULL(rule3,0) AS rule3
            FROM 
            (
              SELECT DISTINCT returnColumn, ruleColumn, bool
                FROM dbo.tableRules
            ) AS SourceTable
           PIVOT 
           (
             AVG(bool) 
             FOR ruleColumn IN (rule1,rule2,rule3)
           ) As pt
        )pt2
        JOIN dbo.otherRuleTable ort
          ON ort.returnColumn = pt2.returnColumn;

The syntax is the same, no matter what join type you use. Check out my series to see which type is right for you.

Left Join Pivot Table with Column from different Table

1 Answers1