Pivot query issue

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

The following should get you the desired results...

SELECT 
	td.De_User,
	D08_S01 = SUM(CASE WHEN td.De_Days = 8  AND td.De_Status = 1 THEN 1 ELSE 0 END),
	D08_S02 = SUM(CASE WHEN td.De_Days = 8  AND td.De_Status = 2 THEN 1 ELSE 0 END),
	D30_S01 = SUM(CASE WHEN td.De_Days = 30 AND td.De_Status = 1 THEN 1 ELSE 0 END),
	D30_S02 = SUM(CASE WHEN td.De_Days = 30 AND td.De_Status = 2 THEN 1 ELSE 0 END)
FROM 
	#tblDetails td
GROUP BY 
	td.De_User;
1 Like

Why doing it simple when you can make it complicated :frowning: It appears I went the wrong way, thank you Jason

LOL... No worries. Glad to help.

To ask the follow up questions to make sure you understand... do you know what this method is called and how it works?