Solved

Add a row in stored procedure...

Posted on 2013-05-13
5
234 Views
Last Modified: 2013-05-13
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: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28125687.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

Open in new window

0
Comment
Question by:Mike Eghtebas
  • 3
  • 2
5 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 39162698
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39162734
Hi  Sharath_123,

Other than one minor point, it works fine.

The problem is in a column counts where there is 2 items it shows <3>

It increases all column value counts except the very first one.

Thanks.
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 39162794
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39162981
Thank you. It is excellent.

In a new question I will post shortly, I need to handle sort issue with this.

When I call:  select * from tblTarget Order By 1

I get the result in the attached image for:
create  TABLE tblSource(
f1 varchar(10)
,f2 varchar(10)
,f3 varchar(10)
,f4 varchar(10)
,f5 varchar(10)
)

create  TABLE tbltarget(
f1 varchar(10)
,f2 varchar(10)
,f3 varchar(10)
,f4 varchar(10)
,f5 varchar(10)
)
--select * from tblSource
insert into tblSource (f1, f2, f3, f4, f5)
values('C','R','Q','Z', 'W'),(NULL,'X',NULL,'GZ', 'HW'),('D',NULL,NULL,'CZ', 'MW'),(NULL,'P','P','IZ', 'UW'),(NULL,'UX',NULL,'GZ', NULL)

Open in new window


If we could add a new sort column called f0 (int type no index) and number it like:

tblTarget
==========
f0    f1   f2    f3  ....
---   ----
0   <4>
0   <tbd>
1      A
2      B
.
.

Then we can sort in  by:
select * from tblTarget Order By f0

I will post a link here.

Thank you.
tblTarget.png
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39163017
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now