Search code examples
sqlsql-serversql-server-2008common-table-expression

Joining Two CTE's with Pivots

This is going to be a long one so i'm very sorry. I had a search and i could find anything that fit the bill for the fault I'm having.

I currently need to figure out how to join two CTE's which both are pivoted to allow for the data to be stitched back into a table.

Unfortunately I can't get the tables updated to be in the correct format required as the data has to be maintained in its original design for auditing purposes.

Currently the CTES's are

CTE1 Files:

  • ID
  • REF
  • UserID
  • SubmitDate
  • SubmitBy
  • a1
  • a2
  • a3
  • a4
  • a5
  • comments

CTE2 Fields:

  • ID
  • q1
  • q2
  • q3
  • q4
  • q5

The thought is to do the join off the ID Field on both CTE's 1 and 2 However I am struggling to get the join to work.

Structure of the Join Logic

WITH CTE1 as 
(
    ** CTE QUERY **
),
CTE2 AS 
(
    ** CTE QUERY **
)

Select
 *
FROM
        CTE1
JOIN    CTE2    on CTE1.id = CTE2.id

I believe that this could potentially be due to the complex CTE used to separate a value from the table. The CTE's are extremely similar to a previous post i made ( Splitting Strings with Regular Terms to Columns ) which the data is pivoted (will include below)

Expected Return

enter image description here

Thanks for any help. Also if you require additional information and i'll include.

Currently It doesn't return anything I receive the following error message 

Msg 319, Level 15, State 1, Line 61
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Solution

  • The syntax of your query is incorrect.

    It has to be something like this:

    WITH 
CTE1 AS
(
    SELECT ...
    FROM ...
)
,CTE2 AS 
(
    SELECT ...
    FROM ...
)
,CTE3 AS 
(
    SELECT ...
    FROM ...
)
,CTE4 AS 
(
    SELECT ...
    FROM ...
)
SELECT ...
FROM ...
;

    But you have something like this:

    WITH 
CTE1 AS
(
    SELECT ...
    FROM ...
)
SELECT ...
FROM ...
,CTE2 AS 
(
    SELECT ...
    FROM ...
)
SELECT ...
FROM ...
SELECT ...
FROM ...
;

    I hope you see the difference.

    WITH should be followed be several CTEs followed by a single final SELECT. You can't have standalone SELECT in the middle of the query without wrapping it into a CTE.