Manipulate the pivot function generated table data

Question

enter image description here In the above image, the cols Jan-21 , Feb-21 are dynamically generated and the data is populated using pivot function in SQL. I want to update the 0's to No and 1's to Yes .

I am using this query to get the data.

The tables mon_year_data contains dynamically generated columnsname from Jan-21 to today date. enter image description here This is the second table which we are using in the below query, enter image description here



DECLARE @cols AS NVARCHAR(MAX)='',@cols2 AS NVARCHAR(MAX)='', @query AS NVARCHAR(MAX)=''

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(mon_year) from #mon_year_data FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1,'')
Select @cols2 = Coalesce(@cols2+',','') 
+ 'case when ' + replace(replace(QUOTENAME(mon_format),']',''),'[','') + '=''1'' then ''Received'' else ''Not Received'' end as ' 
+ replace(replace(QUOTENAME(mon_format),']',''),'[','') from (select (mon_format) from #first_four_columns group by mon_format) as tab
--print @cols2

set @query = 'SELECT PARTNER_NAME, CLIENT_PARTNER_CODE, POS, Inventory, [Started Reporting], ' + @cols2 + ' from 
                                    ( select f.partner_name,
                                    f.client_partner_code, 
                                    f.POS, f.Inventory, f.[Started Reporting], f.mon_format
                                    from #first_four_columns f 
                                    left join #mon_year_data myd 
                                    on f.mon_format = myd.mon_year
                                    ) x
                                    pivot 
                                    (
                                       count(x.mon_format)
                                       for x.mon_format in (' + @cols + ')
                                    ) AS p '
--print @query
execute(@query)

I am using @cols2 variable but it is not working.

I am unable to manipulate the pivot generated data in the dynamically generated columns. I need to replace 0's and 1's to 'No' and 'Yes'. Please help.

Output given in the print statement.

select  PARTNER_NAME, CLIENT_PARTNER_CODE, POS, Inventory, [Started Reporting], [Jan-21],[Feb-21],[Mar-21],[Apr-21],[May-21],[Jun-21],[Jul-21],[Aug-21],[Sep-21],[Oct-21],[Nov-21],[Dec-21],[Jan-22],[Feb-22],[Mar-22],[Apr-22],[May-22],[Jun-22],[Jul-22],[Aug-22],[Sep-22],[Oct-22],[Nov-22],[Dec-22] from 
                                ( select f.partner_name,
                                f.client_partner_code, 
                                f.POS, f.Inventory, f.[Started Reporting], f.mon_format
                                from #first_four_columns f 
                                left join #mon_year_data myd 
                                on f.mon_format = myd.mon_year
                                ) x
                                pivot 
                                (
                                   count(x.mon_format)
                                   for x.mon_format in ([Jan-21],[Feb-21],[Mar-21],[Apr-21],[May-21],[Jun-21],[Jul-21],[Aug-21],[Sep-21],[Oct-21],[Nov-21],[Dec-21],[Jan-22],[Feb-22],[Mar-22],[Apr-22],[May-22],[Jun-22],[Jul-22],[Aug-22],[Sep-22],[Oct-22],[Nov-22],[Dec-22])
                                ) AS p

Completion time: 2022-12-27T21:26:51.1093350+00:00

As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Dec 27 '22 at 21:21
Since you are already familiar with using the print statement for debugging dynamic SQL, please show us the SQL that your print statement generated. — Dale K, Dec 27 '22 at 21:22

T N · Answer 1 · 2022-12-27T22:13:04.020

I do not believe there is an easy way. You will just have to add CASE statements to your final select list.

select ...
    CASE WHEN [Jan-21] > 0 THEN 'Yes' ELSE 'No' END AS [Jan-21],
    CASE WHEN [Feb-21] > 0 THEN 'Yes' ELSE 'No' END AS [Feb-21],
    CASE WHEN [Mar-21] > 0 THEN 'Yes' ELSE 'No' END AS [Mar-21],
...

Although it would be nice if PIVOT allowed more complex expressions in the PIVOT expression such as:

...
pivot (
    CASE WHEN count(x.mon_format) > 0 THEN 'Yes' ELSE 'No' END  -- Not allowed.
    for ...
)

, but this just isn't allowed. Similar questions have been asked regarding mapping NULL values here, but the answer was just to apply the mapping to each individual result, sometimes using dynamic SQL.

For your case, something like:

DECLARE @mapped_cols AS NVARCHAR(MAX)=''
SELECT @mapped_cols = STUFF((
        SELECT ', CASE WHEN '
            + QUOTENAME(mon_year)
            + ' > 0 THEN ''Yes'' ELSE ''No'' END AS '
            + QUOTENAME(mon_year)
        from #mon_year_data
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    , 1, 1,'')

and include @mapped_cols in your final dynamic SQL select list.

Manipulate the pivot function generated table data

1 Answers1