Syntax errors with UNPIVOT and JOIN in FROM clause

Question

I'm using SQL Server Express (with advanced services), version 10.50.1600.1.

I need to use a UNPIVOT to create multiple rows from a single ROW, and also need to use JOINS, to get fields from master data tables.

I can make 2 working but separate SELECT statements. The first is the UNPIVOT, the second contains the JOINs. But I simply cannot make them work together! When I put the UNPIVOT followed by the JOINs, I'm always getting error 8156 (Column was specified multiple times) or 4104 (The multi-part identifier could not be bound)...

Sample data is in this SQL Fiddle: http://sqlfiddle.com/#!2/452de/1

Here's the sample data

Table TIMESHEET_LINE

PROJECT_ID  DATE1   DATE7   HOUR1   HOUR2   HOUR3   HOUR4   HOUR5   HOUR6   HOUR7
16  2011-10-03  2011-10-09  0   0   0,5 0   0   0   0
18  2011-10-03  2011-10-09  0   0   0,01111111  0   0   0   0
18  2011-10-03  2011-10-09  0   0   0,001944444 0   0   0   0
28  2011-10-03  2011-10-09  0   0   0   2   0   0   0
13  2011-10-03  2011-10-09  0   0   0   0   0   0   0
18  2011-10-03  2011-10-09  0   0   0   0   1,250556    0   0
18  2011-10-03  2011-10-09  0   0   0   0   0,7141666   0   0

Table Project

Table PROJECT 

PROJECT_ID  PROJECT_NUMBER  PROJECT_NAME
13  30013   Control Venta Negativa
16  24464   Zonas de Transporte
18  PRBRCOM2012_12  Garantia
28  24466   Embalagens Retornáveis

The expected output is:

PROJECT_NUMBER PROJECT_NAME DATE HOUR
30013   Control Venta Negativa  2011-10-03  0
30013   Control Venta Negativa  2011-10-04  0
30013   Control Venta Negativa  2011-10-05  0,5
30013   Control Venta Negativa  2011-10-06  0
30013   Control Venta Negativa  2011-10-07  0
30013   Control Venta Negativa  2011-10-08  0
30013   Control Venta Negativa  2011-10-09  0
PRBRCOM2012_12  Garantia    2011-10-03  0
PRBRCOM2012_12  Garantia    2011-10-04  0
PRBRCOM2012_12  Garantia    2011-10-05  0,01111111
PRBRCOM2012_12  Garantia    2011-10-06  0
PRBRCOM2012_12  Garantia    2011-10-07  0
PRBRCOM2012_12  Garantia    2011-10-08  0
PRBRCOM2012_12  Garantia    2011-10-09  0

The SQL statement to UNPIVOT the TIMESHEET_LINE table:

SELECT
    [USER_ID], 
    [PROJECT_ID], 
    [TASK_GROUP_ID], 
    [TASK_ID],
    DATEADD
    (
        DAY,
        CAST( RIGHT([WeekDay],1)
            AS int) - 1, 
        Date1
    ) As 'Date',

    SUM(Hours) AS 'Hours'

FROM [aceproject].[dbo].[TIMESHEET_LINE]
    UNPIVOT
    (
        Hours for [WeekDay] in (HOUR1, HOUR2, HOUR3, HOUR4, HOUR5, HOUR6, HOUR7)
    ) upvt 

WHERE
    [USER_ID] = '18'

GROUP BY
    [USER_ID], 
    [PROJECT_ID], 
    [TASK_GROUP_ID], 
    [TASK_ID],
    [WeekDay],
    [Date1]

The SQL Statement to JOIN the tables:

SELECT 
    TSL.PROJECT_ID,
    P.PROJECT_NUMBER,
    P.PROJECT_NAME

FROM [TIMESHEET_LINE] AS TSL

    INNER  JOIN [aceproject].[dbo].[PROJECT] AS P with (nolock) ON P.PROJECT_ID = TSL.PROJECT_ID

I tried to put them together on several ways. First by using JOINs and UNPIVOT in the same SELECT statement:

SELECT
    P.PROJECT_NUMBER,
    P.PROJECT_NAME,
    TSL.[USER_ID], 
    TSL.[PROJECT_ID], 
    TSL.[TASK_GROUP_ID], 
    TSL.[TASK_ID],
    DATEADD
    (
        DAY,
        CAST( RIGHT(upvt.[WeekDay],1)
            AS int) - 1, 
        TSL.Date1
    ) As 'Date',

    SUM(upvt.Hours) AS 'Hours'

FROM [TIMESHEET_LINE] AS TSL

    INNER  JOIN PROJECT AS P with (nolock) ON P.PROJECT_ID = TSL.PROJECT_ID

    UNPIVOT
    (
        Hours for [WeekDay] in (HOUR1, HOUR2, HOUR3, HOUR4, HOUR5, HOUR6, HOUR7)
    ) upvt 

WHERE
    TSL.[USER_ID] = '18'

GROUP BY
    TSL.[USER_ID], 
    TSL.[PROJECT_ID], 
    TSL.[TASK_GROUP_ID], 
    TSL.[TASK_ID],
    upvt.[WeekDay],
    TSL.[Date1]

Also tried selecting from the separate statements:

SELECT 
    Project_ID,
    Client_Country,
    Project_Create_By,
    Resource_Country,
    Resource_IPN,

    DATEADD
    (
        DAY, 
        CAST( RIGHT([WeekDay],1)
            AS int) - 1, 
        Date1
    ) As 'Date',

    Hours AS 'Hours'

FROM
(
    SELECT

        TSL.[USER_ID], 
        TSL.[PROJECT_ID], 
        TSL.[TASK_GROUP_ID], 
        TSL.[TASK_ID],

        TSL.PROJECT_ID AS 'Project_ID',
        Left(C.CLIENT_NAME,2) AS 'Client_Country',
        LTRIM(PU.USERNAME)  AS 'Project_Create_By',
        LEFT(UG1.USER_GROUP_NAME,2) AS 'Resource_Country',
        LTRIM(U.USERNAME) AS 'Resource_IPN',

        TSL.DATE1,
        TSL.HOUR1,
        TSL.HOUR2,
        TSL.HOUR3,
        TSL.HOUR4,
        TSL.HOUR5,
        TSL.HOUR6,
        TSL.HOUR7

    FROM [aceproject].[dbo].[TIMESHEET_LINE] AS TSL

        INNER  JOIN [aceproject].[dbo].[PROJECT] AS P with (nolock) ON P.PROJECT_ID = TSL.PROJECT_ID
        LEFT  JOIN CLIENT   AS C with (nolock) ON C.CLIENT_ID   = P.CLIENT_ID
        LEFT  JOIN USERS    AS PU   with (nolock) ON (PU.COMPANY_ID = P.COMPANY_ID   and PU.USER_ID = P.PROJECT_CREATOR_ID)
        LEFT  JOIN USERS    AS U    with (nolock) ON U.USER_ID        = TSL.USER_ID
        LEFT  JOIN USER_GROUP   AS UG1  with (nolock) ON (UG1.COMPANY_ID  = U.COMPANY_ID   and UG1.USER_GROUP_ID  = U.USER_GROUP_ID)

) d

UNPIVOT
(
    Hours for [WeekDay] in (HOUR1, HOUR2, HOUR3, HOUR4, HOUR5, HOUR6, HOUR7)
) upvt 

Also tried the opposite, with the UNPIVOT as sub SELECT. But nothing worked.

Thanks in advance!

Answer 1

Ok, I sorted it out, finally! =) First you make the JOIN, and then you make a nested select over it:

SELECT TOP 100
    TIMESHEET_LINE_ID,
    PROJECT_NUMBER,
    DATEADD
    (
        DAY,
        CAST( RIGHT([WeekDay],1)
            AS int) - 1, 
        Date1
    ) As 'Date',

    Hours AS 'Hours'

FROM 
(
    SELECT 
        P.PROJECT_NUMBER,
        TSL.TIMESHEET_LINE_ID,
        TSL.DATE1,
        TSL.HOUR1,
        TSL.HOUR2,
        TSL.HOUR3,
        TSL.HOUR4,
        TSL.HOUR5,
        TSL.HOUR6,
        TSL.HOUR7
    FROM
        [aceproject].[dbo].[TIMESHEET_LINE] AS TSL
        INNER JOIN PROJECT  AS P with (nolock) ON P.PROJECT_ID    = TSL.PROJECT_ID 
) d
    UNPIVOT
    (
        Hours for [WeekDay] in (HOUR1, HOUR2, HOUR3, HOUR4, HOUR5, HOUR6, HOUR7)
    ) upvt