Sman每周访问Party Wise
What I am trying to achieve here is to Group the Rows with same LedId_Sman and LedId_Party and then have a pivoted View of the Days visited in a single Row.
我在这里想要实现的是使用相同的LedId_Sman和LedId_Party对行进行分组，然后在单行中调用所访问的天数视图。
My Actual Table
我的实际表
LedId_Party LedId_Sman VisitDay
----------- ----------- --------
426 296 3
426 296 6
441 296 2
Query I am Using
查询我正在使用
SELECT LedId_Party, LedId_Sman,[1]as Sun,[2]as Mon,[3] as Tue,[4] as Wed,[5] as Thu,[6] as Fri,[7] as Sat
FROM dbo.tbl_WeeklyVisit
Pivot(
Count(VisitDay)
For VisitDay in
([1],[2],[3],[4],[5],[6],[7]
))AS PiviotTable
This is currently what I Get
这是我目前得到的
LedId_Party LedId_Sman Mon Tue Wed Thu Fri Sat Sun
426 297 0 0 0 0 1 0 0
426 297 0 1 0 0 0 0 0
This is what I want the Output to be.
这就是我想要的输出。
LedId_Party LedId_Sman Mon Tue Wed Thu Fri Sat Sun
426 297 0 1 0 0 1 0 0
I am Fairly New To SQL so an insight on how would it work would be very helpful and appreciated.
我对SQL非常新，所以对它如何工作的见解会非常有帮助和赞赏。
1 个解决方案
#1
0
You might have extra columns in the data that are resulting in the extra rows. When using pivot, it is a good practice to have a subquery with only the columns referenced in the pivot:
您可能在数据中有额外的列，这些列会产生额外的行。使用pivot时，最好使用子查询，只包含pivot中引用的列：
SELECT LedId_Party, LedId_Sman, [1] as Sun,[2] as Mon, [3] as Tue,
[4] as Wed, [5] as Thu, [6] as Fri, [7] as Sat
FROM (SELECT LedId_Party, LedId_Sman, VisitDay
FROM dbo.tbl_WeeklyVisit
) wv
PIVOT(Count(VisitDay)
For VisitDay in ([1], [2], [3], [4], [5], [6], [7]
)
) as PiviotTable
#1
0
You might have extra columns in the data that are resulting in the extra rows. When using pivot, it is a good practice to have a subquery with only the columns referenced in the pivot:
您可能在数据中有额外的列，这些列会产生额外的行。使用pivot时，最好使用子查询，只包含pivot中引用的列：
SELECT LedId_Party, LedId_Sman, [1] as Sun,[2] as Mon, [3] as Tue,
[4] as Wed, [5] as Thu, [6] as Fri, [7] as Sat
FROM (SELECT LedId_Party, LedId_Sman, VisitDay
FROM dbo.tbl_WeeklyVisit
) wv
PIVOT(Count(VisitDay)
For VisitDay in ([1], [2], [3], [4], [5], [6], [7]
)
) as PiviotTable