Code for alternative to Pivot Function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Code for alternative to Pivot Function

Author

Topic

matthew.callanan
Starting Member

2 Posts

Posted - 2013-10-18 : 07:18:11

Hi,

I have the written the following code and I need it to be paid out with the month names as columns (rather than rows) - can anyone advise?

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET DATEFORMAT DMY
DECLARE @Start_Date DATETIME
DECLARE @End_Date DATETIME

SET @Start_Date = '01/03/2013'
SET @End_Date = '01/08/2013'

SELECT
case
WHEN [Clinic] LIKE '%PHYSIOWORLD%' THEN 'PHYSIOWORLD'
WHEN [Clinic] NOT LIKE '%PHYSIOWORLD%' THEN 'NON-PHYSIOWORLD'
ELSE [Clinic]
END AS [TEST],
count ([Create_Date]),
DATENAME(month,CONVERT(NVARCHAR, [dbo].[ufn_ConvertEpochIntToGeorgianDate]([Create_Date]), 0)) AS [Referral Month]

FROM
[dbo].[CM_CAS_Case]

WHERE
[Create_Date] BETWEEN [dbo].[ufn_ConvertGeorgianDateToEpochInt](@Start_Date) AND [dbo].[ufn_ConvertGeorgianDateToEpochInt](@End_Date)

GROUP BY case
WHEN [Clinic] LIKE '%PHYSIOWORLD%' THEN 'PHYSIOWORLD'
WHEN [Clinic] NOT LIKE '%PHYSIOWORLD%' THEN 'NON-PHYSIOWORLD'
ELSE [Clinic]
END
,
DATENAME(month,CONVERT(NVARCHAR, [dbo].[ufn_ConvertEpochIntToGeorgianDate]([Create_Date]), 0))

ORDER BY [Referral Month] ASC, [TEST] DESC

RESULT =

TEST

(No column name)

Referral Month

PHYSIOWORLD

2936

April

NON-PHYSIOWORLD

6829

April

NULL

207

April

PHYSIOWORLD

3361

July

NON-PHYSIOWORLD

8374

July

NULL

294

July

PHYSIOWORLD

3058

June

NON-PHYSIOWORLD

7212

June

NULL

312

June

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-18 : 08:05:36

[code]
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET DATEFORMAT DMY
DECLARE @Start_Date DATETIME
DECLARE @End_Date DATETIME

SET @Start_Date = '01/03/2013'
SET @End_Date = '01/08/2013'

SELECT *
FROM
(
SELECT
case
WHEN [Clinic] LIKE '%PHYSIOWORLD%' THEN 'PHYSIOWORLD'
WHEN [Clinic] NOT LIKE '%PHYSIOWORLD%' THEN 'NON-PHYSIOWORLD'
ELSE [Clinic]
END AS [TEST],
count ([Create_Date]) AS Cnt,
DATENAME(month,CONVERT(NVARCHAR, [dbo].[ufn_ConvertEpochIntToGeorgianDate]([Create_Date]), 0)) AS [Referral Month]

FROM
[dbo].[CM_CAS_Case]

WHERE
[Create_Date] BETWEEN [dbo].[ufn_ConvertGeorgianDateToEpochInt](@Start_Date) AND [dbo].[ufn_ConvertGeorgianDateToEpochInt](@End_Date)

GROUP BY case
WHEN [Clinic] LIKE '%PHYSIOWORLD%' THEN 'PHYSIOWORLD'
WHEN [Clinic] NOT LIKE '%PHYSIOWORLD%' THEN 'NON-PHYSIOWORLD'
ELSE [Clinic]
END
,
DATENAME(month,CONVERT(NVARCHAR, [dbo].[ufn_ConvertEpochIntToGeorgianDate]([Create_Date]), 0))
)t
PIVOT (SUM(Cnt) FOR [Referral Month] IN ([Jan],[Feb],[Mar],[Apr],...[Dec]))p
ORDER BY [TEST] DESC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

matthew.callanan
Starting Member

2 Posts

Posted - 2013-10-18 : 08:52:02

Thanks. Ideally looking for an alternative to the Pivot table function (as our Extranet system does not support then)

I have actually come up with something that appears to work

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET DATEFORMAT DMY
DECLARE @Start_Date DATETIME
DECLARE @End_Date DATETIME

SET @Start_Date = '01/03/2013'
SET @End_Date = '01/09/2013'

SELECT
case
WHEN [Clinic] LIKE '%PHYSIOWORLD%' THEN 'PHYSIOWORLD'
WHEN [Clinic] NOT LIKE '%PHYSIOWORLD%' THEN 'NON-PHYSIOWORLD'
ELSE [Clinic]
END AS [REFFERED TO],
case
WHEN (DATENAME(month,CONVERT(NVARCHAR, [dbo].[ufn_ConvertEpochIntToGeorgianDate]([Create_Date]),0))) LIKE 'March' THEN count([Create_Date])
END
AS [March],
case
WHEN (DATENAME(month,CONVERT(NVARCHAR, [dbo].[ufn_ConvertEpochIntToGeorgianDate]([Create_Date]),0))) LIKE 'April' THEN count([Create_Date])
END
AS [April],
case
WHEN (DATENAME(month,CONVERT(NVARCHAR, [dbo].[ufn_ConvertEpochIntToGeorgianDate]([Create_Date]),0))) LIKE 'May' THEN count([Create_Date])
END
AS [MAY],
case
WHEN (DATENAME(month,CONVERT(NVARCHAR, [dbo].[ufn_ConvertEpochIntToGeorgianDate]([Create_Date]),0))) LIKE 'June' THEN count([Create_Date])
END
AS [June],
case
WHEN (DATENAME(month,CONVERT(NVARCHAR, [dbo].[ufn_ConvertEpochIntToGeorgianDate]([Create_Date]),0))) LIKE 'July' THEN count([Create_Date])
END
AS [July],
case
WHEN (DATENAME(month,CONVERT(NVARCHAR, [dbo].[ufn_ConvertEpochIntToGeorgianDate]([Create_Date]),0))) LIKE 'August' THEN count([Create_Date])
END
AS [AUGUST],
DATENAME(month,CONVERT(NVARCHAR, [dbo].[ufn_ConvertEpochIntToGeorgianDate]([Create_Date]),0)) AS [Month],
month(CONVERT(NVARCHAR, [dbo].[ufn_ConvertEpochIntToGeorgianDate]([Create_Date]))) AS [Date ref]

INTO #Refs4

FROM
[dbo].[CM_CAS_Case]

WHERE
[Create_Date] BETWEEN [dbo].[ufn_ConvertGeorgianDateToEpochInt](@Start_Date) AND [dbo].[ufn_ConvertGeorgianDateToEpochInt](@End_Date)
AND
[ExpertType] LIKE '%PHYSIO%'

GROUP BY case
WHEN [Clinic] LIKE '%PHYSIOWORLD%' THEN 'PHYSIOWORLD'
WHEN [Clinic] NOT LIKE '%PHYSIOWORLD%' THEN 'NON-PHYSIOWORLD'
ELSE [Clinic]
END

,

month(CONVERT(NVARCHAR, [dbo].[ufn_ConvertEpochIntToGeorgianDate]([Create_Date])))
,
DATENAME(month,CONVERT(NVARCHAR, [dbo].[ufn_ConvertEpochIntToGeorgianDate]([Create_Date]), 0))

ORDER BY [Date ref] ASC, [REFFERED TO] ASC

------------

SELECT
[REFFERED TO] AS [PW OR NON PW]
,sum( [MARCH]) AS [MARCH]
,sum ([APRIL]) AS [APRIL]
,sum ([MAY]) AS [MAY]
,sum ([JUNE]) AS [JUNE]
,sum ([JULY]) AS [JULY]
,sum ([AUGUST]) AS [AUGUST]

FROM #Refs4

GROUP BY [REFFERED TO]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources