x

Stored procedure help to display multiple values

i have a table Companiesdata

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

the records 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 other table

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

i wrote a procedure as below:

create proc pr_getlistofcompaniesss (@tparentid varchar(20))

as
b

egin

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 records 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 seperated with comma displaying the record on same row i.e ( advertising,media)

more ▼

asked Aug 08, 2012 at 06:52 AM in Default

avatar image

tsaliki
150 10 10 14

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I hope this helps

 --DDL PART
 DECLARE @Company TABLE
     (
      CompanyName NVARCHAR(50)
     ,Industry NVARCHAR(50)
     )
 
 --DATA INSERTION PART
 INSERT  @Company
         (
          [CompanyName]
         ,[Industry]
         )
         SELECT  'Xyz technologies'
         ,       'Software'
         UNION ALL
         SELECT  'apple Technologies'
         ,       'software'
         UNION ALL
         SELECT  'Sun network'
         ,       'media'
         UNION ALL
         SELECT  'abc Technologies'
         ,       'advertising'
         UNION ALL
         SELECT  'PQR Technnologies'
         ,       'Marketing'
         UNION ALL
         SELECT  'abc Technologies'
         ,       'Media'
         
 --ACTUAL CODE TO GET THE DESIRED OUTPUT        
 SELECT ROW_NUMBER() OVER (ORDER BY [CompanyName]) AutoId,
     [CompanyName],
     (SELECT STUFF(
  (
  SELECT ',' + [Industry] FROM @Company C1 
  WHERE [C1].[CompanyName] = [C].[CompanyName]
  ORDER BY [CompanyName]
  FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)')
  , 1,1,'')
  ) Industry
 FROM @Company AS C
 GROUP BY [CompanyName]
 ORDER BY [CompanyName]
more ▼

answered Aug 08, 2012 at 08:01 AM

avatar image

Usman Butt
13.9k 6 13 21

is it possible to do it in any other way ????? like with the help of stored procedures ?

Aug 08, 2012 at 08:17 AM tsaliki

You can encapsulate it in a procedure. Just change the code according to your base tables/columns etc

Aug 08, 2012 at 08:39 AM Usman Butt

thank you usman ... will check it out now .

Aug 08, 2012 at 09:07 AM tsaliki
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x111
x30
x27

asked: Aug 08, 2012 at 06:52 AM

Seen: 1096 times

Last Updated: Aug 08, 2012 at 09:54 AM

Copyright 2016 Redgate Software. Privacy Policy