| Author |
 Topic  |
|
|
tsaliki
Starting Member
India
11 Posts |
 Posted - 08/08/2012 : 07:02:43
|
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
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 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)
|
|
|
lionofdezert
Aged Yak Warrior
Pakistan
864 Posts |
Posted - 08/08/2012 : 08:02:08
|
CREATE TABLE [dbo].[Companiesdata]
(
[Company Name] NVARCHAR(255),
[Industry] VARCHAR(40),
[ParentId] INT NULL,
)
INSERT INTO [dbo].[Companiesdata]
SELECT 'Xyz', 'technologies Software', 1
UNION ALL
SELECT 'apple', 'Technologies software', 1
UNION ALL
SELECT 'Sun', 'network media', 2
UNION ALL
SELECT 'abc', 'Technologies advertising', 4
UNION ALL
SELECT 'PQR', 'Technnologies Marketing', 5
UNION ALL
SELECT 'abc', 'Technologies Media', 4
CREATE TABLE dbo.companiesss
(
autoid INT IDENTITY(1, 1),
companyname VARCHAR(MAX),
Industry VARCHAR(MAX)
)
CREATE PROC pr_getlistofcompaniesss ( @tparentid VARCHAR(20) )
AS
BEGIN
INSERT INTO dbo.companiesss
( companyname, industry )
SELECT DISTINCT
cd.[Company Name],
SUBSTRING(( SELECT ', ' + I.[Industry]
FROM [dbo].[Companiesdata] i
WHERE cd.[Company Name] = I.[Company Name]
FOR
XML PATH('') ), 2, 8000) AS CompanyName
FROM [dbo].[Companiesdata] cd
WHERE parentid IN ( SELECT *
FROM dbo.split(@tparentid) )
EXCEPT
SELECT [companyname], industry
FROM dbo.companiesss
END
--------------------------
http://connectsql.blogspot.com/ |
Edited by - lionofdezert on 08/08/2012 08:02:45 |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 08/08/2012 : 09:51:11
|
the way its written i think you should be calling proc as
pr_getlistofcompaniesss '1,2,4'
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
tsaliki
Starting Member
India
11 Posts |
Posted - 08/09/2012 : 01:36:21
|
@lionofdezert : Thank you for your response,But i am getting the following error.Can you please check and if possible can u help me ?
Msg 116, Level 16, State 1, Procedure pr_getlistofcompaniesss, Line 5
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 08/09/2012 : 10:08:17
|
CREATE PROC pr_getlistofcompaniesss ( @tparentid VARCHAR(20) )
AS
BEGIN
;With New_Companies
AS
(
select [CompanyName],[Industry] from [Companiesdata]
where parentid in(select items from dbo.split(@tparentid,','))
except
select [company name],industry from dbo.companiesss
)
INSERT INTO dbo.companiesss
( companyname, industry )
SELECT c.[companyname],
STUFF((SELECT ',' + Industry
FROM New_Companies
WHERE CompanyName = c.CompanyName
FOR XML PATH('')),1,1,'')
FROM (SELECT DISTINCT [CompanyName] FROM New_Companies) c
END
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Store procedure help
