September 10, 2015 at 4:48 pm
NOTE: I previously asked about something like this but at the time was unable to provide DDL. This time I created a table and populated it with example data so this could be provided.
I have a single table that consist of 4 columns. Entity, ParamName, ParamsValue and ParamiValue. This table stores normalized Late Fee related parameters for apartments. The Entity field contains a code that identifies the apartment complex.
The ParamName in a textual field that contains the name of the parameter that the other 2 fields define the value for; ParamsValue and ParamiValue. If the Late Fee parameter (as named in ParamName is something numerical then the value for that parameter can be found in ParamiValue else its in ParamsValue.
I don't know if 'Pivot' is the correct term to use for describing what I am trying to do because I've looked at the Pivot examples and I don't see how that will work for this.
Using the Table and data as provided below, how would I construct a query so that I get 1 row per Entity in which the columns are the ParamsValue or ParamiValue for the ParamName listed in the column header (for the query)?
Below is the DDL to create the table and populate it.
USE [DBA_UTIL]
CREATE TABLE [dbo].[PARAMEXAMPLE](
[Entity] [varchar](16) NULL,
[ParamName] [varchar](256) NULL,
[ParamsValue] [varchar](256) NULL,
[ParamiValue] [varchar](256) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[PARAMEXAMPLE] ([Entity], [ParamName], [ParamsValue], [ParamiValue]) VALUES (N'abc', N'GraceDays', NULL, N'5')
GO
INSERT [dbo].[PARAMEXAMPLE] ([Entity], [ParamName], [ParamsValue], [ParamiValue]) VALUES (N'abc', N'LateType', NULL, N'1')
GO
INSERT [dbo].[PARAMEXAMPLE] ([Entity], [ParamName], [ParamsValue], [ParamiValue]) VALUES (N'abc', N'LatePerDay', NULL, N'10')
GO
INSERT [dbo].[PARAMEXAMPLE] ([Entity], [ParamName], [ParamsValue], [ParamiValue]) VALUES (N'abc', N'MaxDays', NULL, N'15')
GO
INSERT [dbo].[PARAMEXAMPLE] ([Entity], [ParamName], [ParamsValue], [ParamiValue]) VALUES (N'abc', N'LateMinAmount', NULL, N'15')
GO
INSERT [dbo].[PARAMEXAMPLE] ([Entity], [ParamName], [ParamsValue], [ParamiValue]) VALUES (N'jkl', N'GraceDays', NULL, N'3')
GO
INSERT [dbo].[PARAMEXAMPLE] ([Entity], [ParamName], [ParamsValue], [ParamiValue]) VALUES (N'jkl', N'LateType', NULL, N'2')
GO
INSERT [dbo].[PARAMEXAMPLE] ([Entity], [ParamName], [ParamsValue], [ParamiValue]) VALUES (N'jkl', N'LatePerDay', NULL, N'10')
GO
INSERT [dbo].[PARAMEXAMPLE] ([Entity], [ParamName], [ParamsValue], [ParamiValue]) VALUES (N'jkl', N'MaxDays', NULL, N'15')
GO
INSERT [dbo].[PARAMEXAMPLE] ([Entity], [ParamName], [ParamsValue], [ParamiValue]) VALUES (N'jkl', N'LateMinAmount', NULL, NULL)
GO
INSERT [dbo].[PARAMEXAMPLE] ([Entity], [ParamName], [ParamsValue], [ParamiValue]) VALUES (N'xyz', N'GraceDays', NULL, N'5')
GO
INSERT [dbo].[PARAMEXAMPLE] ([Entity], [ParamName], [ParamsValue], [ParamiValue]) VALUES (N'xyz', N'LateType', NULL, N'1')
GO
INSERT [dbo].[PARAMEXAMPLE] ([Entity], [ParamName], [ParamsValue], [ParamiValue]) VALUES (N'xyz', N'LatePerDay', NULL, N'10')
GO
INSERT [dbo].[PARAMEXAMPLE] ([Entity], [ParamName], [ParamsValue], [ParamiValue]) VALUES (N'xyz', N'MaxDays', NULL, N'12')
GO
INSERT [dbo].[PARAMEXAMPLE] ([Entity], [ParamName], [ParamsValue], [ParamiValue]) VALUES (N'xyz', N'LateMinAmount', NULL, N'25')
GO
INSERT [dbo].[PARAMEXAMPLE] ([Entity], [ParamName], [ParamsValue], [ParamiValue]) VALUES (N'ops', N'GraceDays', NULL, N'3')
GO
INSERT [dbo].[PARAMEXAMPLE] ([Entity], [ParamName], [ParamsValue], [ParamiValue]) VALUES (N'ops', N'LateType', NULL, N'5')
GO
INSERT [dbo].[PARAMEXAMPLE] ([Entity], [ParamName], [ParamsValue], [ParamiValue]) VALUES (N'ops', N'LatePerDay', NULL, N'20')
GO
INSERT [dbo].[PARAMEXAMPLE] ([Entity], [ParamName], [ParamsValue], [ParamiValue]) VALUES (N'ops', N'LateMinAmount', NULL, N'0.0')
GO
INSERT [dbo].[PARAMEXAMPLE] ([Entity], [ParamName], [ParamsValue], [ParamiValue]) VALUES (N'abc', N'LateDesc', N'Flat', NULL)
GO
INSERT [dbo].[PARAMEXAMPLE] ([Entity], [ParamName], [ParamsValue], [ParamiValue]) VALUES (N'jkl', N'LateDesc', N'PercentRent', NULL)
GO
INSERT [dbo].[PARAMEXAMPLE] ([Entity], [ParamName], [ParamsValue], [ParamiValue]) VALUES (N'ops', N'LateDesc', N'Flat', NULL)
GO
INSERT [dbo].[PARAMEXAMPLE] ([Entity], [ParamName], [ParamsValue], [ParamiValue]) VALUES (N'xyz', N'LateDesc', N'PercentBalance', NULL)
GO
Kindest Regards,
Just say No to Facebook!September 11, 2015 at 1:26 am
September 11, 2015 at 8:28 am
Or at Jeff's series on cross tabs and pivots: Part 1[/url] & Part 2[/url].
Personally, I try it to keep it simple by using the concatenation method explained in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
And the dynamic pivots are formed by 3 statements (declare, set and execute).
DECLARE @sql nvarchar(max)
SET @sql = --Columns to group by
'SELECT Entity '
--Columns to pivot
+ ( SELECT DISTINCT
CHAR( 10) + CHAR(9)
+ ',MAX( CASE WHEN ParamName = ' + QUOTENAME(ParamName, '''')
+ ' THEN ISNULL( [ParamsValue], [ParamiValue]) END) AS ' + QUOTENAME(ParamName)
FROM [PARAMEXAMPLE]
FOR XML PATH(''),TYPE).value('.','varchar(max)')
--From and group by clause
+ CHAR( 10) + ' FROM PARAMEXAMPLE '
+ CHAR( 10) + 'GROUP BY Entity; ';
PRINT @sql;
EXECUTE sp_executesql @sql;
September 11, 2015 at 9:18 am
Eirikur Eiriksson (9/11/2015)
Have a look at Aaron Bertrand's article Script to create dynamic PIVOT queries in SQL Server[/url]😎
I believe this is EXACTLY what I was looking for. It covers handling a dynamic number of values. I have only lanced over it and tested the code and not read it all the way through but from what I see I believe this will do the trick.
Thank you
Kindest Regards,
Just say No to Facebook!September 11, 2015 at 9:19 am
Luis Cazares (9/11/2015)
Or at Jeff's series on cross tabs and pivots: Part 1[/url] & Part 2[/url].Personally, I try it to keep it simple by using the concatenation method explained in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
And the dynamic pivots are formed by 3 statements (declare, set and execute).
DECLARE @sql nvarchar(max)
SET @sql = --Columns to group by
'SELECT Entity '
--Columns to pivot
+ ( SELECT DISTINCT
CHAR( 10) + CHAR(9)
+ ',MAX( CASE WHEN ParamName = ' + QUOTENAME(ParamName, '''')
+ ' THEN ISNULL( [ParamsValue], [ParamiValue]) END) AS ' + QUOTENAME(ParamName)
FROM [PARAMEXAMPLE]
FOR XML PATH(''),TYPE).value('.','varchar(max)')
--From and group by clause
+ CHAR( 10) + ' FROM PARAMEXAMPLE '
+ CHAR( 10) + 'GROUP BY Entity; ';
PRINT @sql;
EXECUTE sp_executesql @sql;
Thanks Luis
Kindest Regards,
Just say No to Facebook!Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply