SQL query to select matrix user-day count-CodePudding

I have the next data

User	Day
User1	Monday
User2	Monday
User3	Monday
User2	Tuesday
User3	Tuesday

I'm looking for a query that will produce the next result

User	Monday	Tuesday
User1	1	0
User2	1	1
User3	1	1

CodePudding user response：

You can use pivot like below;

WITH q AS (
  SELECT  aUser = 'User1', aDay = 'mon', aCount = 1
  UNION ALL   SELECT 'User1','mon',1
  UNION ALL   SELECT 'User1','mon',1
  UNION ALL   SELECT 'User2','mon',1
  UNION ALL   SELECT 'User3','tue',1
  UNION ALL   SELECT 'User2','wed',1
  UNION ALL   SELECT 'User3','thu',1
  UNION ALL   SELECT 'User2','thu',1
  UNION ALL   SELECT 'User2','thu',1
  UNION ALL   SELECT 'User2','mon',1
)
SELECT  aUser
        , mon = ISNULL(mon,0)
        , tue = ISNULL(tue,0)
        , wed = ISNULL(wed,0)
        , thu = ISNULL(thu,0)
        , fri = ISNULL(fri,0)
        , sat = ISNULL(sat,0)
        , sun = ISNULL(sun,0)
FROM    (
          SELECT  *
          FROM    q
        ) s
PIVOT   (sum(aCount) FOR aDay IN (mon,tue,wed,thu,fri,sat,sun)) pvt

CodePudding user response：

You can use PIVOT to change rows with columns:

for just these two days:

SELECT *
FROM (
    SELECT [day], [user]
    FROM table1
) as s
PIVOT
(
    count([day])
    FOR [day] IN (Monday, Tuesday)
)AS pvt

for all days of the week:

SELECT *
FROM (
    SELECT [day], [user]
    FROM table1
) as s
PIVOT
(
    count([day])
    FOR [day] IN (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday)
)AS pvt

CodePudding user response：

I use a conditional aggregation approach better than a PIVOT one, because I feel its syntax is less complicated and it is less limited (and eficiency is not worse).

select [User],
  SUM(case when Day = 'Monday' then 1 else 0 end) Monday,
  SUM(case when Day = 'Tuesday' then 1 else 0 end) Tuesday
from YourTable
group by [User]

By the way, you should not use User as a field name, because it is a reserved word.

DBFiddle: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=1f7d06eaa4ac8f23498a71ff552a6a4f