lightningfuzion2 lightningfuzion2 - 2 months ago 10
SQL Question

Sql Pivot By Grouping two columns

Sman Weekly Visit 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.

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.

Answer

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:

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