How to write a stored procedure for this code

Question

I have a table Companiesdata

CREATE TABLE [dbo].[Companiesdata]
(
[Company Name] nvarchar(255),
[Industry] varchar(40),
[ParentId] int NULL,
)

and the rows are

CompanyName               Industry          Parent ID  
--------------------------------------------------------
Xyz technologies          Software             1
apple Technologies        software             1
Sun network               media                2
abc Technologies          advertising          4
PQR Technnologies         Marketing            5
abc Technologies          Media                4

I have another table

create table dbo.companiesss
(
autoid int identity(1,1),
companyname varchar(max),
Industry  varchar(max)
)

I wrote a procedure like this:

create proc pr_getlistofcompaniesss (@tparentid varchar(20))   
as 
begin
   insert into dbo.companiesss(companyname, industry) 
      select  
          [CompanyName], [Industry] 
      from 
          [Companiesdata]
      where 
          parentid in (select items from dbo.split(@tparentid,',')) 
      except
      select company name, industry 
      from dbo.companiesss 
end

The output is as below:

pr_getlistofcompaniesss 1,2,4

the rows are displayed as

AutoID    Company name              Industry
---------------------------------------------------
  1       apple Technologies        software
  2       Sun network               Media
  3       xyz Technologies          software
  4       abc Technologies          advertising
  5       abc technologies          media

instead my output should be as below:

pr_getlistofcompaniesss 1,2,4

AutoID    Company name              Industry
---------------------------------------------------
  1       apple Technologies        software
  2       Sun network               Media
  3       xyz Technologies          software
  4       abc Technologies          advertising,media

i.e if I have the same company (here abc technologies) with different industries name, then the industry field should be separated with comma displaying the record on same row i.e (advertising, media)

Have a look here: http://stackoverflow.com/questions/10405362/concatenate-multiple-rows-from-multiple-tables you have 2 options there - use XML, or create an aggregate CLR function — YS., Aug 08 '12 at 11:43
Or, since this looks like prepared data for a report, move the presentation work to the reporting engine, where it belongs. In short: the result you obtain is correct, you should group values together in your report, not in the database. — Diego, Aug 08 '12 at 11:59

score 0 · Answer 1 · answered Aug 08 '12 at 11:53

0

I've created a scalar function to achieve similar results, and then cross apply the function your stored procedure. Code is below:

CREATE  FUNCTION [dbo].[fnListIndustry] (@ParentID varchar(10))
RETURNS VARCHAR(1000)
AS
BEGIN 
DECLARE @IndustryList   varchar(1000)
SELECT  @IndustryList   = COALESCE(@IndustryList + ',', '') + rtrim(dbo.Companiesdata.Industry)
FROM    dbo.Companiesdata 
WHERE   dbo.Companiesdata.parenid = @ParentID

RETURN  @IndustryList
END

answered Aug 08 '12 at 11:53

Gene

11
3

Beware of scalar functions that use `select` queries. Depending on the size of tables your dealing with, performance can be severely impacted – pyrospade Aug 08 '12 at 12:10

pyrospade · Answer 2 · 2012-08-10T13:54:17.210

0

First, you need to pass in those IDs together in a string, not as separate parameters. Your procedure only accepts one parameter.

pr_getlistofcompaniesss '1,2,4'

Then in your procedure, update your insert clause to below. That will concatenate the industries into one string delimited by commas.

insert into dbo.companiesss(companyname,industry) 
select [CompanyName],
       STUFF((select distinct ',' + [Industry]
              from [Companiesdata] as child
              where child.parentid = parent.parentid
              for xml path ('')),
             1, 2, '')
from [Companiesdata] as parent
join dbo.split(@tparentid,',')
    on (parent.parentid = split.items)

I'm also using a join versus using where ... in ( ... ) syntax as joins generally perform much faster.

edited Aug 10 '12 at 13:54

answered Aug 08 '12 at 12:05

pyrospade

7,870
4
36
52

@ pyrospade: Thank you for ur response but the code which u sent is not working .. The error thrown is Msg 4104, Level 16, State 1, Procedure pr_tr_getlistofcompaniesss, Line 5 .The multi-part identifier "Companiesdata.parentid" could not be bound. – Tsaliki Aug 09 '12 at 05:21
@ pyrospade: Thank you once again but if i execute the code for example consider pr_getlistofcompaniesss '1' then the output for your code is as follows: ID Company name Industry 222 apple Technologies software,software 223 xyz Technologies software,software 224 abc technologies advertising,media Instead i want the output as ID Company name Industry 222 apple Technologies software 223 xyz Technologies software 224 abc technologies advertising,media – Tsaliki Aug 10 '12 at 05:27
Just add the `distinct` keyword to the subquery (which has been updated in the answer). – pyrospade Aug 10 '12 at 13:57
Sorry for the late reply but Thank you for ur answer .. i tried it and its partially correct,but the problem is if i have the same company name ( here abc technologies) with different parentid's then i am not getting the desired result .. any help regarding this please ? – Tsaliki Aug 24 '12 at 10:00
What is your "desired result"? In your example, abc technologies has the same parent id in both rows, so it's hard to know what you are after. – pyrospade Aug 24 '12 at 12:21
Thankyou once again for your interest.If for example i have abc techonlogies with 2 ids (ex:4,6) then when i execute the store procedure pr_getlistofcompaniesss '4,6'then the output i am getting is abc technologies advertising abc technologies media i.e i am getting in 2 rows but my desired output should be commaseparated if the company name is same. – Tsaliki Aug 27 '12 at 07:21
Maybe you can try modifying the subquery where clause to `where child.parentid = parent.parentid or child.CompanyName = parent.CompanyName`. However, you didn't mention that requirement, nor do your tables seem to be set up to do this (no id field for CompanyName), so this is more of a hack. – pyrospade Aug 27 '12 at 11:28
@pyrosade : Thank you for your time.Its just that an example i had given ... I didnt get what you are saying about the ID field for a companyname.Actually if i have two different ids for the same company then also it should get on the same row.. So i asked if u can help me out.Thank you once again but still my problem has not been solved. – Tsaliki Sep 07 '12 at 04:25
Impossible for anyone to solve your issue if we don't have the appropriate information (e.g. your schema). In fact, I think it can be debated that your original problem was solved a long time ago...proper etiquette would be to mark this response as the accepted answer and submit your new questions in a new post. – pyrospade Sep 07 '12 at 11:02

How to write a stored procedure for this code

2 Answers2