I need to achieve something like this
In the actual life I'll need to grab the user's name and the status' description.
Running the SQL shown below I am getting the data for day 8 and I am getting this result
1 1 2
2 1 1
3 0 1
5 1 0
Running for day 30 give this result
1 0 1
2 1 0
4 0 1
CREATE TABLE [dbo].[#tblDetails](
[De_Id] [int] NOT NULL,
[De_User] [int] NULL,
[De_Status] [int] NULL,
[De_Days] [int] NULL
CONSTRAINT [PK_#tblDetails] PRIMARY KEY CLUSTERED
(
[De_Id] ASC
)
)
GO
INSERT INTO [dbo].[#tblDetails] ([De_Id] ,[De_User] ,[De_Status] ,[De_Days]) VALUES (1,1,1,8)
GO
INSERT INTO [dbo].[#tblDetails] ([De_Id] ,[De_User] ,[De_Status],[De_Days]) VALUES (2,1,2,8)
GO
INSERT INTO [dbo].[#tblDetails] ([De_Id] ,[De_User] ,[De_Status],[De_Days]) VALUES (4,1,2,8)
GO
INSERT INTO [dbo].[#tblDetails] ([De_Id] ,[De_User] ,[De_Status],[De_Days]) VALUES (5,1,2,30)
GO
INSERT INTO [dbo].[#tblDetails] ([De_Id] ,[De_User] ,[De_Status],[De_Days]) VALUES (6,2,1,8)
GO
INSERT INTO [dbo].[#tblDetails] ([De_Id] ,[De_User] ,[De_Status],[De_Days]) VALUES (7,2,1,30)
GO
INSERT INTO [dbo].[#tblDetails] ([De_Id] ,[De_User] ,[De_Status],[De_Days]) VALUES (8,3,2,8)
GO
INSERT INTO [dbo].[#tblDetails] ([De_Id] ,[De_User] ,[De_Status],[De_Days]) VALUES (9,4,2,30)
GO
INSERT INTO [dbo].[#tblDetails] ([De_Id] ,[De_User] ,[De_Status],[De_Days]) VALUES (10,2,2,8)
GO
INSERT INTO [dbo].[#tblDetails] ([De_Id] ,[De_User] ,[De_Status],[De_Days]) VALUES (11,5,1,8)
GO
Select * from #tblDetails
GO
--Declare necessary variables
DECLARE @SQLQuery AS NVARCHAR(MAX)
DECLARE @PivotColumns AS NVARCHAR(MAX)
--Get unique values of pivot column
SELECT @PivotColumns= COALESCE(@PivotColumns + ',','') + QUOTENAME(De_Status)
FROM (SELECT DISTINCT De_Status FROM [dbo].#tblDetails) AS PivotExample
--Create the dynamic query with all the values for
--pivot column at runtime
SET @SQLQuery =
N'SELECT De_User, ' + @PivotColumns + '
FROM [dbo].[#tblDetails]
PIVOT( COUNT(De_Id)
FOR De_Status IN (' + @PivotColumns + ')) AS P WHERE De_Days=30 ORDER By De_USer'
--Execute dynamic query
EXEC sp_executesql @SQLQuery
DROP TABLE #tblDetails
GO