Add a row in stored procedure...

Stored procedure at http://sqlfiddle.com/#!3/e2bd2/1

is called via:
exec abcd 'tblSource','tblTarget',5
select * from tblTarget

Currently, it doesn't included having the second row  <tbd> at each column as shown below. It also adds Null entries for the missing data which is better to be replaced with an empty string.

tbaTarget
========
f1           f2
-------    --------
 <3>       <2>          'note: 2 here means there are two values in this column (P and X)
<tbd>   <tbd>
   A            P
   D            X
   E           Null         ' how to replace Null with empty, so it will not show at all

Question: How can I add this row to tblTarget inside the stored procedure? And how to replace Null values with empty, so that they will not show.

for additional reference, see: https://www.experts-exchange.com/questions/28125687/T-SQL-help-short-script.html

Thank you.

I am including the code here in case the link above has been altered.

create TABLE tblSource(
f1 varchar(10)
,f2 varchar(10)
,fn varchar(10)
)

insert into tblSource (f1,f2,fn)
values('C','R','Q'),('B','X',NULL),('D',NULL,NULL),('B','P','P'),('A','X',NULL)
go

create procedure abcd
(
 @tblsource VARCHAR(100)
,@tbltarget varchar(100)
,@no_of_cols int
)
AS
BEGIN
declare @select varchar(max),@where varchar(max),@sql varchar(max)

-- Get the column names
SELECT @where = STUFF(( SELECT ',['+ COLUMN_NAME+']'
                FROM  INFORMATION_SCHEMA.columns where table_name = @tblsource and ORDINAL_POSITION <= @no_of_cols
               ORDER BY   ',['+ COLUMN_NAME+']'
                 FOR XML PATH('')), 1, 1, '') 

-- Create the target table
select @sql = '
IF OBJECT_ID('''+@tblTarget+''') IS NOT NULL
    DROP TABLE ' + @tblTarget +'
	select '+@where+ '--, 0 AS OrderBy 
into '+@tblTarget+' from ' + @tblSource + ' where 1 = 0'
exec(@sql)

select @select = substring(select_list,1,len(select_list)-5)
  from (
select stuff((select 'SELECT ''' + COLUMN_NAME + ''' colname,'+COLUMN_NAME+' data FROM ' + @tblsource + ' WHERE ISNULL('+COLUMN_NAME+','''') <> '''' UNION '  
                from INFORMATION_SCHEMA.columns where table_name = @tblsource and ORDINAL_POSITION <= @no_of_cols
               order by COLUMN_NAME 
                 for xml path(''), 
                type).value('.','nvarchar(max)')
               , 1, 0, '') as select_list) t1 


select @sql = '
;with cte as (
select *,ROW_NUMBER() over (partition by colname order by data) rn
from ('+@select+') t1)
insert ' + @tblTarget + '
 select distinct '+@where+'--,1
  from cte
pivot (max(data) for colname in ('+@where+') ) p'
exec(@sql)

--select @sql = '
--INSERT INTO ' + @tblTarget + '
--SELECT COUNT(DISTINCT ' +REPLACE(@where,',','),COUNT(DISTINCT ') + ')FROM ' + @tblTarget


select @sql = 'INSERT INTO ' 
+ @tblTarget 
+ ' SELECT '
+ '''<''' 
+ '+ convert(varchar,COUNT(DISTINCT ' 
+ REPLACE(@where,',',')) + ' + '''>''' + ' ,' + '''<''' + ' + convert(varchar,COUNT(DISTINCT ')
+ ')) + ' 
+ '''>''' 
+ ' FROM '
+ @tblTarget


exec(@sql)



--select @sql = '
--SELECT ' + @where + '
--FROM ' + @tblTarget + '
--ORDER BY OrderBy'
--exec(@sql)


end

Select allOpen in new window