Question

把我的头等同起来。这里的一些专家或许会看到我做错了什么?

我有以下时间安排:

CREATE TABLE TimeSheetTime(
    TimeSheetTimeID int IDENTITY(1,1) NOT NULL,
    TimeSheetItemID int NOT NULL,
    OffsetToEntryDate tinyint NOT NULL,
    Hours float NOT NULL
)

这些数据包括:

INSERT TimeSheetTime (TimeSheetItemID, OffsetToEntryDate, Hours)
SELECT 1,1,7 
UNION SELECT 1,3,1.5
UNION SELECT 1,0,1
UNION SELECT 2,1,0
UNION SELECT 2,4,0
UNION SELECT 2,3,0
UNION SELECT 3,2,0
UNION SELECT 3,4,1
UNION SELECT 3,5,2
UNION SELECT 3,1,2
UNION SELECT 3,2,4
UNION SELECT 4,3,5
UNION SELECT 4,5,16
UNION SELECT 4,2,1
UNION SELECT 5,3,0.5
UNION SELECT 5,4,1
UNION SELECT 5,3,1
UNION SELECT 5,4,4
UNION SELECT 6,5,4
UNION SELECT 6,1,0.5
UNION SELECT 6,2,3.2
UNION SELECT 7,3,0.2
UNION SELECT 7,5,1.5
UNION SELECT 7,1,0.1
UNION SELECT 8,3,0.4
UNION SELECT 8,5,0.5
UNION SELECT 8,1,0.3
UNION SELECT 8,3,1
UNION SELECT 9,5,2
UNION SELECT 9,3,0.5

Now I d like to return a dataset that has TimeSheetItemID followed by the PIVOT SUM of Hours, arranged in columns of OffsetToEntryDate. ie :

TimeSheetItemID | 0 | 1 | 2 | 3 | 4 | 5 | 6

我尝试如下:

SELECT *
FROM TimeSheetTime
pivot 
(
    SUM(Hours)
    FOR OffSetToEntryDate
    IN (0,1,2,3,4,5,6)
) as TST

欢迎任何建议。

Answer 1

而是使用:

SELECT *
FROM TimeSheetTime
pivot 
(
    SUM(Hours)
    FOR OffSetToEntryDate
    IN ([0],[1],[2],[3],[4],[5],[6])
) as TST

您希望在您的PIVOT表中使用的栏目必须放在方括号中<代码>[......]。

取得这些成果:

TimeSheetTimeID TimeSheetItemID 0   1   2   3   4   5   6
1   1   1   NULL    NULL    NULL    NULL    NULL    NULL
2   1   NULL    7   NULL    NULL    NULL    NULL    NULL
3   1   NULL    NULL    NULL    1.5 NULL    NULL    NULL
4   2   NULL    0   NULL    NULL    NULL    NULL    NULL
5   2   NULL    NULL    NULL    0   NULL    NULL    NULL
6   2   NULL    NULL    NULL    NULL    0   NULL    NULL
7   3   NULL    2   NULL    NULL    NULL    NULL    NULL
8   3   NULL    NULL    0   NULL    NULL    NULL    NULL
9   3   NULL    NULL    4   NULL    NULL    NULL    NULL
10  3   NULL    NULL    NULL    NULL    1   NULL    NULL
11  3   NULL    NULL    NULL    NULL    NULL    2   NULL
12  4   NULL    NULL    1   NULL    NULL    NULL    NULL
13  4   NULL    NULL    NULL    5   NULL    NULL    NULL
14  4   NULL    NULL    NULL    NULL    NULL    16  NULL
15  5   NULL    NULL    NULL    0.5 NULL    NULL    NULL
16  5   NULL    NULL    NULL    1   NULL    NULL    NULL
17  5   NULL    NULL    NULL    NULL    1   NULL    NULL
18  5   NULL    NULL    NULL    NULL    4   NULL    NULL
19  6   NULL    0.5 NULL    NULL    NULL    NULL    NULL
20  6   NULL    NULL    3.2 NULL    NULL    NULL    NULL
21  6   NULL    NULL    NULL    NULL    NULL    4   NULL
22  7   NULL    0.1 NULL    NULL    NULL    NULL    NULL
23  7   NULL    NULL    NULL    0.2 NULL    NULL    NULL
24  7   NULL    NULL    NULL    NULL    NULL    1.5 NULL
25  8   NULL    0.3 NULL    NULL    NULL    NULL    NULL
26  8   NULL    NULL    NULL    0.4 NULL    NULL    NULL
27  8   NULL    NULL    NULL    1   NULL    NULL    NULL
28  8   NULL    NULL    NULL    NULL    NULL    0.5 NULL
29  9   NULL    NULL    NULL    0.5 NULL    NULL    NULL
30  9   NULL    NULL    NULL    NULL    NULL    2   NULL

页: 1

Answer 2

页: 1 看来,对于一些类型的外地价值而言,PIVOT可能无法做到。以下著作是好的。现在我不得不指出,如何在我的申请范围内开展工作。

DECLARE @TimeSheetTime TABLE (
    TimeSheetItemID int NOT NULL,
    OffsetToEntryDate varchar(4) NOT NULL,
    Hours float NOT NULL
)

INSERT @TimeSheetTime (TimeSheetItemID, OffsetToEntryDate, Hours)
SELECT 1, a1 ,7 
UNION SELECT 1, a3 ,1.5
UNION SELECT 1, a0 ,1
UNION SELECT 2, a1 ,0
UNION SELECT 2, a4 ,0
UNION SELECT 2, a3 ,0
UNION SELECT 3, a2 ,0
UNION SELECT 3, a4 ,1
UNION SELECT 3, a5 ,2
UNION SELECT 3, a1 ,2
UNION SELECT 3, a2 ,4
UNION SELECT 4, a3 ,5
UNION SELECT 4, a5 ,16
UNION SELECT 4, a2 ,1
UNION SELECT 5, a3 ,0.5
UNION SELECT 5, a4 ,1
UNION SELECT 5, a3 ,1
UNION SELECT 5, a4 ,4
UNION SELECT 6, a5 ,4
UNION SELECT 6, a1 ,0.5
UNION SELECT 6, a2 ,3.2
UNION SELECT 7, a3 ,0.2
UNION SELECT 7, a5 ,1.5
UNION SELECT 7, a1 ,0.1
UNION SELECT 8, a3 ,0.4
UNION SELECT 8, a5 ,0.5
UNION SELECT 8, a1 ,0.3
UNION SELECT 8, a3 ,1
UNION SELECT 9, a5 ,2
UNION SELECT 9, a3 ,0.5

SELECT * FROM @TimeSheetTime
PIVOT (
    SUM(Hours)
    FOR OffsetToEntryDate
    IN (a0,a1,a2,a3,a4,a5,a6)
) AS p

友情链接