Pivot group by SQL query

POSH Guy 0
Viewed 32

I have a table to track the mailbox and groups. 1 mailbox will have 3 different groups. I want to check each day's connected status of all mailboxes and groups. I have created the below query but it returns multiple rows. I want to aggregate data like the one below. Could someone please help!

Select cast (CreatedDate as Date), Connected, GroupOrMbx, GroupType 
from [dbo].[Mbx_test]
group by cast (CreatedDate as Date), Connected, GroupOrMbx, GroupType

Expected output:

enter image description here

Table & data

CREATE TABLE [dbo].[Mbx_test](
    [GroupOrMbx] [varchar](10) NOT NULL,    
    [GroupName] [varchar](255) NULL,
    [GroupEmail] [varchar](255) NULL,
    [GroupType] [varchar](10) NULL,
    [MBXName] [varchar](255) NULL,
    [MBXEmail] [varchar](255) NULL,
    [Connected] [bit] NOT NULL,
    [CreatedDate] [datetime] NOT NULL
) 

INSERT INTO Mbx_test
VALUES 
('mbx', NULL, NULL,NULL,'mbx1','mbx1@test.com',1,'2022-09-22'),
('group', 'group1','group1@test.com','W','mbx1','mbx1@test.com',1,'2022-09-22'),
('group', 'group2','group2@test.com','M','mbx1','mbx1@test.com',1,'2022-09-22'),
('group', 'group3','group3@test.com','R','mbx1','mbx1@test.com',1,'2022-09-22'),

('mbx', NULL, NULL,NULL,'mbx2','mbx2@test.com',1,'2022-09-22'),
('group', 'group4','group4@test.com','W','mbx2','mbx2@test.com',1,'2022-09-22'),
('group', 'group5','group5@test.com','M','mbx2','mbx2@test.com',1,'2022-09-22'),
('group', 'group6','group6@test.com','R','mbx2','mbx2@test.com',1,'2022-09-22'),

('mbx', NULL, NULL,NULL,'mbx3','mbx3@test.com',0,'2022-09-22'),
('group', 'group7','group7@test.com','W','mbx3','mbx3@test.com',0,'2022-09-22'),
('group', 'group8','group8@test.com','M','mbx3','mbx3@test.com',0,'2022-09-22'),
('group', 'group9','group9@test.com','R','mbx3','mbx3@test.com',0,'2022-09-22'),

('mbx', NULL, NULL,NULL,'mbx4','mbx4@test.com',0,'2022-09-22'),
('group', 'group10','group10@test.com','W','mbx4','mbx4@test.com',0,'2022-09-22'),
('group', 'group11','group11@test.com','M','mbx4','mbx4@test.com',0,'2022-09-22'),
('group', 'group12','group12@test.com','R','mbx4','mbx4@test.com',0,'2022-09-22')

Code is saved here https://dbfiddle.uk/WRW7xKeO

pivot tsql sql-server sql
2 Answers

A statement using conditional aggregation returns the expected results:

SELECT 
   CreatedDate, 
   Connected,
   COUNT(CASE WHEN GroupOrMbx = 'mbx' THEN GroupOrMbx END) AS [Mbx],
   COUNT(CASE WHEN GroupOrMbx = 'group' THEN GroupOrMbx END) AS [Group],
   COUNT(CASE WHEN GroupType = 'W' THEN GroupType END) AS [W],
   COUNT(CASE WHEN GroupType = 'M' THEN GroupType END) AS [M],
   COUNT(CASE WHEN GroupType = 'R' THEN GroupType END) AS [R]
FROM Mbx_test
GROUP BY CreatedDate, Connected
Zhorov0

It's not pretty but you can do the whole thing using a load of sub-queries

WITH TestDates AS
(
  SELECT DISTINCT CreatedDate FROM Mbx_test
)
SELECT
   CreatedDate,
   'Yes' Connected,
   (SELECT COUNT(*) FROM Mbx_Test WHERE Mbx_Test.CreatedDate = TestDates.CreatedDate AND GroupOrMbx = 'mbx' AND Connected = 1) Mbx,
   (SELECT COUNT(*) FROM Mbx_Test WHERE Mbx_Test.CreatedDate = TestDates.CreatedDate AND GroupOrMbx = 'group' AND Connected = 1) [Group],
   (SELECT COUNT(*) FROM Mbx_Test WHERE Mbx_Test.CreatedDate = TestDates.CreatedDate AND GroupOrMbx = 'group' AND GroupType = 'W' AND Connected = 1) W,
   (SELECT COUNT(*) FROM Mbx_Test WHERE Mbx_Test.CreatedDate = TestDates.CreatedDate AND GroupOrMbx = 'group' AND GroupType = 'M' AND Connected = 1) M,
   (SELECT COUNT(*) FROM Mbx_Test WHERE Mbx_Test.CreatedDate = TestDates.CreatedDate AND GroupOrMbx = 'group' AND GroupType = 'R' AND Connected = 1) R
FROM
   TestDates
UNION
SELECT
   CreatedDate,
   'No' Connected,
   (SELECT COUNT(*) FROM Mbx_Test WHERE Mbx_Test.CreatedDate = TestDates.CreatedDate AND GroupOrMbx = 'mbx' AND Connected = 0) Mbx,
   (SELECT COUNT(*) FROM Mbx_Test WHERE Mbx_Test.CreatedDate = TestDates.CreatedDate AND GroupOrMbx = 'group' AND Connected = 0) [Group],
   (SELECT COUNT(*) FROM Mbx_Test WHERE Mbx_Test.CreatedDate = TestDates.CreatedDate AND GroupOrMbx = 'group' AND GroupType = 'W' AND Connected = 0) W,
   (SELECT COUNT(*) FROM Mbx_Test WHERE Mbx_Test.CreatedDate = TestDates.CreatedDate AND GroupOrMbx = 'group' AND GroupType = 'M' AND Connected = 0) M,
   (SELECT COUNT(*) FROM Mbx_Test WHERE Mbx_Test.CreatedDate = TestDates.CreatedDate AND GroupOrMbx = 'group' AND GroupType = 'R' AND Connected = 0) R
FROM
   TestDates
Martin Brown0
Related