sql-server - FROM 子句中的 UNPIVOT 和 JOIN 语法错误

Question

我正在使用版本 10.50.1600.1 的 SQL Server Express(带有高级服务)。

我需要使用 UNPIVOT 从单个 ROW 创建多行，还需要使用 JOINS 从主数据表中获取字段。

我可以使 2 个工作但单独的 SELECT 语句。第一个是 UNPIVOT，第二个包含 JOIN。但我根本无法让他们一起工作!当我将 UNPIVOT 后跟 JOIN 时，我总是收到错误 8156(多次指定列)或 4104(无法绑定(bind)多部分标识符)...

示例数据在此 SQL Fiddle 中:http://sqlfiddle.com/#!2/452de/1

这是示例数据

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 

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

预期的输出是:

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

对 TIMESHEET_LINE 表进行 UNPIVOT 的 SQL 语句:

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]

加入表的 SQL 语句:

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

我试图以多种方式将它们组合在一起。首先在同一个 SELECT 语句中使用 JOIN 和 UNPIVOT:

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]

还尝试从单独的语句中进行选择:

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 

也尝试了相反的方法，将 UNPIVOT 作为子 SELECT。
但没有任何效果。

提前致谢!

Answer 1

好的，我终于解决了! =)
首先进行 JOIN，然后在其上进行嵌套选择:

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