SQLTeam.com | Weblogs | Forums

Pivot query issue


#1

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


#2

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;

#3

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


#4

LOL... No worries. Glad to help.


#5

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