Trouble with dynamic pivot table

Other SQL Server Topics
1

I'm trying to create a simple dynamic pivot table in my sql.
below are the mock fields and table name with the results:

Select Distinct
APPR_CD
,CHCK_DATE
,CNTRCT_PAY_AMT

From dbo.MHR_EXT_XPAYD_PAY_DETAIL

Where
FISC_YR = '2024'
and AGCY_CD = '650'
and FRNG_PAY = 'N'
and APPR_CD = '1839'

Results look like this:
APPR_CD CHCK_DATE CNTRCT_PAY_AMT
1839 2023-07-14 00:00:00.000 -2375.00
1839 2023-07-14 00:00:00.000 -68.51
1839 2023-07-14 00:00:00.000 68.51
1839 2023-07-14 00:00:00.000 2375.00
1839 2023-07-31 00:00:00.000 -383.66
1839 2023-07-31 00:00:00.000 54.81
1839 2023-07-31 00:00:00.000 109.61
1839 2023-07-31 00:00:00.000 219.24
1839 2023-07-31 00:00:00.000 2375.00
1839 2023-08-15 00:00:00.000 2375.00

I want them to look like this (I've left off several columns at the end due to space):

                       7/14/2023        7/14/2023      7/14/2023     7/14/2023       7/31/2023      7/31/2023

APPR_CD $(2,375.00) $(68.51) $68.51 $2,375.00 $(383.66) $54.81

Is there a simple way to get the sql set up to achieve these results?

2

That's not a PIVOT, that's a transposition. You're not aggregating multiple rows into a single column. Since you would have multiple column headings with the same name (the CHCK_DATE values), you'll struggle to do this in T-SQL, if it can be done at all.

Pure row/column transposition can be done in Excel, maybe also in SSRS, but I wouldn't know how.

3

Not sure if if makes a difference but the sample query I gave actually has more fields. I just removed all but 3 fields for sake of room.

4

Understood. The fields you supplied in the example were enough, it's still a pure transposition, and it's really just a presentation issue. If you had hundreds of rows that would transpose to hundreds of columns, it will be very unwieldy to do this in SQL.

Also, what happens if APPR_CD has more than 1 distinct value? Do you generate another row? What if APPR_CD = 1840 has an entirely different date range, like months or years before? If there were dozens of APPR_CD values, each with small date spans but overall covering an entire year, this kind of transposition would lead to tons of empty cells, and would be confusing if dates are repeated as headers.

5

What you mentioned is true, there would be more than one distinct value on those fields and the query would pull in some rather large numbers. Might have to rethink this one. My main goal was to just put the date results as columns and have the data show under those (knowing the data results could get large). Everything I've searched for online kept pointing to Pivot. but from there I needed to go a step further because the dates could change each time it's run, that's where the 'dynamic' pivot came in, so I wouldn't have to keep manually entering the dates in a WHERE or Pivot statement.

6

Trying another example here to see if may work better?

So this query works and does bring in the results in the last table like I want to see them, with the dates as columns.
My main goal however is to not have to type in the dates in the IN statement in the Pivot part. Is there a method or code that would work for this?

SELECT

APPR_CD

,convert(varchar(10), (convert(datetime,CHCK_DATE )), 101) 'Check Date'

,sum(CNTRCT_PAY_AMT) 'Amount'

INTO #first

From mzproddw.dw1.dbo.MHR_EXT_XPAYD_PAY_DETAIL

Where

FISC_YR = '2024'

and AGCY_CD = '650'

and FRNG_PAY = 'N'

and APPR_CD = '2149'

GROUP BY

APPR_CD

,convert(varchar(10), (convert(datetime,CHCK_DATE )), 101)

SELECT DISTINCT [Check Date]

INTO #second

FROM #first

SELECT * FROM #second

SELECT * FROM (

SELECT

APPR_CD,

[Check Date],

Amount

FROM #first) AS Payroll

PIVOT (

SUM(Amount)

FOR [Check Date]

IN (

[07/14/2023], --I want to be able to run without having to enter the dates--

[07/31/2023],

[08/15/2023]

)

) AS PivotTable

DROP TABLE #first, #second