UNPIVOT和JOIN在FROM子句中出现语法错误

Question

我正在使用SQL Server Express（带高级服务），版本为10.50.1600.1。

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

我可以制作两个工作但分开的SELECT语句。第一个是UNPIVOT，第二个包含JOIN。但是我无法使它们一起工作！当我将UNPIVOT放在JOIN之后时，我总是收到错误8156（列被指定多次）或4104（无法绑定多部分标识符）...

这里有一个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

UNPIVOT TIMESHEET_LINE 表的 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作为子查询语句，但是都没有成功。

提前感谢！

- Rafael

---

你期望的输出是什么？ - Sarath Subramanian

嗨，Sarath，我在描述中添加了预期输出。 这些是字段： PROJECT_NUMBER PROJECT_NAME DATE HOUR - Rafael

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 

- Rafael