February 13, 2017 at 3:21 pm
I'm assuming this technique has a commonly used name/term and has been solved many times and if that's the case just point me in the right direction.
I have a table where each row consists of financial data or activity encompassing an Entity + Period (month) + Book (i.e. CASH) + Account (i.e. Income or Expense account) . I need to pivot this data so that instead of rows I have columns and the number of columns needs to be dynamic. One time I may need to show 12 columns for 1 years worth activity and another I may need to list multiple years so the column count must be dynamic.
EXAMPLE:
For the entity named ABC for the Account 3000 for the CASH books I have 12 rows of data representing Activity for Jan 2016 thru Dec 2016. Currently I can list each month as a row and show a years worth of activity via 12 rows. What I need is to pivot the financial portion based on the Month column (dtMonth) so that Jan thru Dec are listed left to right; the value that is stored in dMTD.
NOTE: If I need to list 2 years worth of data, Jan 2015 thru Dec 2016 then I would need to list 24 columns of dMTD. I could hard code something to show a fixed number of columns but I need for the columns to be dynamic based on the criteria applied in the query. For example if the query was SELECT * FROM TABLE WHERE dtMonth BETWEEN '2014/01/01' and '2014/06/01' then the results would list 6 columns, Jan 2014 thru June 2014. I do hope this description accurately details what I am trying to achieve,.
Bellow is the T-Sql to re-create the table I'm using . If you need the T-SQl to create some sample data s well let me know. Not sure if that is necessary for this or not.
CREATE TABLE [dbo].[TOTALS]( [sEntityCode] [varchar](16) NOT NULL,
[sEntityName] [varchar](256) NOT NULL,
[dtMonth] [smalldatetime] NOT NULL,
[sBookName] [varchar](8) NOT NULL,
[sAcctCode] [varchar](8) NOT NULL,
[sAcctName] [varchar](256) NOT NULL,
[dMTD] [decimal](18, 2) NOT NULL,
[dYTD] [decimal](18, 2) NOT NULL,
CONSTRAINT [IX_TOTALS] UNIQUE NONCLUSTERED ( [sEntityCode] ASC, [dtMonth] ASC, [sBookName] ASC, [sAcctCode] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Kindest Regards,
Just say No to Facebook!February 13, 2017 at 3:53 pm
Is there any reason why you have to do this in T-SQL? SSRS Matrices or an Excel Pivot Table would easily achieve this goal without having the need for Dynamic SQL, and possibly create something that could be very difficult for the optimiser to perform (depending on how large your dataset is).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 14, 2017 at 9:24 am
Thom A - Monday, February 13, 2017 3:53 PMIs there any reason why you have to do this in T-SQL? SSRS Matrices or an Excel Pivot Table would easily achieve this goal without having the need for Dynamic SQL, and possibly create something that could be very difficult for the optimiser to perform (depending on how large your dataset is).
The software we use does not support SSRS or Excel but does support any T-SQL (2008R2) compliant query/code so I have to make this work within T-SQL.
That said I have gotten %90 of the way there using PIVOT (since posting my message yesterday). I did not think I could use PIVOT . The %10 left to go is dealing with NULL values.
Thanks
Kindest Regards,
Just say No to Facebook!February 14, 2017 at 11:56 am
YSLGuru - Tuesday, February 14, 2017 9:24 AMThom A - Monday, February 13, 2017 3:53 PMIs there any reason why you have to do this in T-SQL? SSRS Matrices or an Excel Pivot Table would easily achieve this goal without having the need for Dynamic SQL, and possibly create something that could be very difficult for the optimiser to perform (depending on how large your dataset is).The software we use does not support SSRS or Excel but does support any T-SQL (2008R2) compliant query/code so I have to make this work within T-SQL.
That said I have gotten %90 of the way there using PIVOT (since posting my message yesterday). I did not think I could use PIVOT . The %10 left to go is dealing with NULL values.
Thanks
And exactly how does one determine how many columns you need?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 14, 2017 at 11:57 am
And what do you have to use for field names for those columns, given that you don't know in advance how many you need?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 14, 2017 at 12:39 pm
Something like this?
WITH Months AS(
SELECT DISTINCT dtMonth
FROM [TOTALS]
WHERE dtMonth BETWEEN '2014/01/01' and '2014/06/01'
)
SELECT N'SELECT sEntityCode
,sEntityName
,sBookName
,sAcctCode
,sAcctName' + CHAR(13)
+ ( SELECT CHAR(9) + ',CASE WHEN dtMonth = ' + QUOTENAME( CONVERT(char(8), dtMonth, 112), '''')
+ ' THEN dMTD END AS ' + QUOTENAME( 'dMTD' + CONVERT(char(8), dtMonth, 112)) + CHAR(13)
+ CHAR(9) + ',CASE WHEN dtMonth = ' + QUOTENAME( CONVERT(char(8), dtMonth, 112), '''')
+ ' THEN dYTD END AS ' + QUOTENAME( 'dYTD' + CONVERT(char(8), dtMonth, 112)) + CHAR(13)
FROM dbo.TOTALS
FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)')
+ 'FROM TOTALS
GROUP BY sEntityCode
,sEntityName
,sBookName
,sAcctCode
,sAcctName';
January 9, 2018 at 8:40 am
Luis Cazares - Tuesday, February 14, 2017 12:39 PMSomething like this?
WITH Months AS(
SELECT DISTINCT dtMonth
FROM [TOTALS]
WHERE dtMonth BETWEEN '2014/01/01' and '2014/06/01'
)
SELECT N'SELECT sEntityCode
,sEntityName
,sBookName
,sAcctCode
,sAcctName' + CHAR(13)
+ ( SELECT CHAR(9) + ',CASE WHEN dtMonth = ' + QUOTENAME( CONVERT(char(8), dtMonth, 112), '''')
+ ' THEN dMTD END AS ' + QUOTENAME( 'dMTD' + CONVERT(char(8), dtMonth, 112)) + CHAR(13)
+ CHAR(9) + ',CASE WHEN dtMonth = ' + QUOTENAME( CONVERT(char(8), dtMonth, 112), '''')
+ ' THEN dYTD END AS ' + QUOTENAME( 'dYTD' + CONVERT(char(8), dtMonth, 112)) + CHAR(13)
FROM dbo.TOTALS
FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)')
+ 'FROM TOTALS
GROUP BY sEntityCode
,sEntityName
,sBookName
,sAcctCode
,sAcctName';
I realize your last reply is 2 years past but I'm revisiting this and have a Question about your sample code. At the start you create MONTHS but don't then use it. Is MONTHS supposed to be joined to the dbo.TOTALS table in the subsequent query?
Kindest Regards,
Just say No to Facebook!January 9, 2018 at 10:09 am
YSLGuru - Tuesday, January 9, 2018 8:40 AMI realize your last reply is 2 years past but I'm revisiting this and have a Question about your sample code. At the start you create MONTHS but don't then use it. Is MONTHS supposed to be joined to the dbo.TOTALS table in the subsequent query?
No. Actually, Months should be used instead of TOTALS. Even better would be to have a Months table.
Here's a corrected version of the sample code.
DECLARE @SQL nvarchar(MAX);
WITH Months AS(
SELECT DISTINCT dtMonth
FROM dbo.[TOTALS]
WHERE dtMonth BETWEEN '20140101' and '20140601'
)
SELECT @SQL = N'SELECT sEntityCode
,sEntityName
,sBookName
,sAcctCode
,sAcctName' + CHAR(13)
+ ( SELECT CHAR(9) + REPLACE( ',SUM(CASE WHEN dtMonth = ''yyyymmdd'' THEN dMTD ELSE 0 END) AS [dMTDyyyymmdd]' + CHAR(13)
+ CHAR(9) + ',SUM( CASE WHEN dtMonth = ''yyyymmdd'' THEN dYTD ELSE 0 END) AS [dYTDyyyymmdd]', 'yyyymmdd', CONVERT(char(8), dtMonth, 112)) + CHAR(13)
FROM Months
FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)')
+ 'FROM dbo.TOTALS
GROUP BY sEntityCode
,sEntityName
,sBookName
,sAcctCode
,sAcctName;';
PRINT @SQL;
EXEC sp_executesql @SQL;
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply