Pivot group by SQL query-CodePudding

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:

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','[email protected]',1,'2022-09-22'),
('group', 'group1','[email protected]','W','mbx1','[email protected]',1,'2022-09-22'),
('group', 'group2','[email protected]','M','mbx1','[email protected]',1,'2022-09-22'),
('group', 'group3','[email protected]','R','mbx1','[email protected]',1,'2022-09-22'),

('mbx', NULL, NULL,NULL,'mbx2','[email protected]',1,'2022-09-22'),
('group', 'group4','[email protected]','W','mbx2','[email protected]',1,'2022-09-22'),
('group', 'group5','[email protected]','M','mbx2','[email protected]',1,'2022-09-22'),
('group', 'group6','[email protected]','R','mbx2','[email protected]',1,'2022-09-22'),

('mbx', NULL, NULL,NULL,'mbx3','[email protected]',0,'2022-09-22'),
('group', 'group7','[email protected]','W','mbx3','[email protected]',0,'2022-09-22'),
('group', 'group8','[email protected]','M','mbx3','[email protected]',0,'2022-09-22'),
('group', 'group9','[email protected]','R','mbx3','[email protected]',0,'2022-09-22'),

('mbx', NULL, NULL,NULL,'mbx4','[email protected]',0,'2022-09-22'),
('group', 'group10','[email protected]','W','mbx4','[email protected]',0,'2022-09-22'),
('group', 'group11','[email protected]','M','mbx4','[email protected]',0,'2022-09-22'),
('group', 'group12','[email protected]','R','mbx4','[email protected]',0,'2022-09-22')

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

CodePudding user response：

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

CodePudding user response：

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