How do append to date value?

Question

Post reply

How do append to date value?

DesNorton

SSC-Insane

Points: 24399
More actions
September 19, 2019 at 6:46 am
Answer
#3681285
ciss1 wrote:
Hi DesNorton,
It's almost working; at least it is appending Year_ to EventYear.
However, I am getting the following error:
Msg 207, Level 16, State 1, Line 30
Invalid column name 'Year_2019'.
Do I need to add that solution to some other place?
Thank you very much for your help.
Aaaggghhh. That's what happens when you code in your head.
In the past, I have used the strategy of having 2 lists - 1 for the data, and 1 for the headers
Again, untested, but try this ...
```
DECLARE @cols       AS NVARCHAR(MAX)
      , @headerCols AS NVARCHAR(MAX)
      , @query      AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT DISTINCT N',' + QUOTENAME(YEAR(t.EventYear))
            FROM Transactions t
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'');

SET @headerCols = STUFF((SELECT DISTINCT N',' + QUOTENAME(YEAR(t.EventYear)) + N' AS ' + QUOTENAME(N'Year_' + CONVERT(nchar(4),YEAR(t.EventYear)))
                  FROM Transactions t
                  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'');

set @query = N'WITH CTE AS
(
    ...
)

SELECT
    ...
Amount,
' + @headerCols + N',
    ...
FROM
    CTE
PIVOT
    (
        MAX(AmountPaid)
        FOR eventyear IN (' + @cols + N')
    ) p 
ORDER BY 
    TransactionID';
```
As Jeff would say ... Shifting gears, take a look at these articles by Jeff Moden. I have found that I get better performance from using CrossTabs instead of PIVOT
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
How to post data/code on a forum to get the best help.

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

ciss1 SSC Veteran Points: 270 More actions · Answer 1

Drew, you are absolutely correct in explaining the static vs the dynamic part, thank you very much.

You said I am using the 'Year_'... incorrectly.

Where should I use it, on the dynamic part (and there are two places where that could be used) or static part?

I tried it on the static part with no luck.

DesNorton SSC-Insane Points: 24399 More actions · Answer 2

You could look at simply adding the "Year_" prefix at the point where you substitute it into the DSQL. I have removed most of the meat from your query to highlite the change.

set @query = N'WITH CTE AS
(
    ...
)

SELECT
    ...
Amount,
' + REPLACE(@cols, '[', '[Year_') + N',
    ...
FROM
    CTE
PIVOT
    (
        MAX(AmountPaid)
        FOR eventyear IN (' + @cols + N')
    ) p 
ORDER BY 
    TransactionID';

How to post data/code on a forum to get the best help.

ciss1 SSC Veteran Points: 270 More actions · Answer 3

Hi DesNorton,

It's almost working; at least it is appending Year_ to EventYear.

However, I am getting the following error:

Msg 207, Level 16, State 1, Line 30

Invalid column name 'Year_2019'.

Do I need to add that solution to some other place?

Thank you very much for your help.

ciss1 SSC Veteran Points: 270 More actions · Answer 4

YEA!

You did it!!

Drew was right, this was no easy solution but you are obviously up to the task.

Here is the result, precisely what I am after:

Year_2019

60.0000

120.0000

60.0000

80.0000

120.0000

I am assuming that when next year kicks in, it will show Year_2020.

Thank you so very much.

5 gloves from me.