Compound Select to concat rows to 1 column
-
Ok I'm trying to select information from two tables that have a 1 to many relationship.
Instead of returning a row for each "many" relationship I want to just concatonate the character value into a single field.
so for instance. Table A has value 1 which links to Table B that has Values X,Y,Z
A standard select would be: Select A.Column1, B.Column2 from A, B where A.Column1 = B.Column1
This would return
1 X
1 Y
1 Z
Instead I want to Return
1 'X,Y,Z' , i.e. 1 row for each parent concatonating the only value I'm retrieving from the child table.
Anyone have a better way than I'm doing it using a massive cursor? I think this can be done using a simple compound select
Thanks
-
IF Object_id('ListTableColumns') > 0
DROP FUNCTION ListTableColumns
GO
CREATE FUNCTION dbo.ListTableColumns (@TableID as int)
RETURNS varchar(8000)
AS
BEGIN
Declare @Items as varchar(8000)
SET @Items = ''
SELECT
@Items = @Items + C.Name + ', '
FROM dbo.SysColumns C
WHERE C.id = @TableID
AND OBJECTPROPERTY(@TableID, 'IsTable') = 1
ORDER BY C.Name
SET @Items = LEFT(@Items, ABS(DATALENGTH(@Items) - 2))
RETURN @Items
END
GO
Select dbo.ListTableColumns(Object_id('SysObjects'))
--base_schema_ver, cache, category, crdate, deltrig, ftcatid, id, indexdel, info, instrig, name, parent_obj, refdate, replinfo, schema_ver, seltrig, stats_schema_ver, status, sysstat, type, uid, updtrig, userstat, version, xtype
DROP FUNCTION ListTableColumns
-
No Cursor, Where there's a will there's a way.
This is a common request. A little review of the existing posts would have revealed a little somthing like this.
create table Widget(pk int, Name varchar(50))
Create table WidgetPart (pk int identity, WidgetPK int, Name varchar(50))
Insert into Widget (pk, Name)
Values (1, 'Flert')
Insert into Widget (pk, Name)
Values (2, 'Molnar')
Insert into Widget (pk, Name)
Values (3, 'Xilt')
Insert into WidgetPart(WidgetPK, Name)
Values(1,'X')
Insert into WidgetPart(WidgetPK, Name)
Values(1,'Y')
Insert into WidgetPart(WidgetPK, Name)
Values(1,'Z')
Insert into WidgetPart(WidgetPK, Name)
Values(2,'X')
Insert into WidgetPart(WidgetPK, Name)
Values(2,'Y')
Insert into WidgetPart(WidgetPK, Name)
Values(3,'X')
Insert into WidgetPart(WidgetPK, Name)
Values(3,'Z')
-- Create an inline Function
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'udf_ReturnWidgetParts')
DROP FUNCTION udf_ReturnWidgetParts
GO
CREATE FUNCTION dbo.udf_ReturnWidgetParts
(@WidgetPK as int)
RETURNS varchar(1000)
AS
BEGIN
Declare @WidgetParts varchar(1000)
-- This Statement Concatenates all names for a widgetpk into a variable, adding a comma.
Select @WidgetParts = coalesce(@WidgetParts,'') + Name + ','
from WidgetPart
where WidgetPK = @WidgetPK
Return @WidgetParts
END
GO
Select *, dbo.udf_ReturnWidgetParts(pk)
from Widget
-- Results
1 Flert X,Y,Z,
2 Molnar X,Y,
3 Xilt X,Z,
Drop table Widget
Drop table WidgetPart
Drop Function udf_ReturnWidgetParts
-
This could also help, for A(Column1) and B(Column1,Column2), if you only have sql7
create procedure p_listBcols
as
select Column1
, min(Column2) Column2
, convert(varchar(80),min(Column2) ) Col2List
into #x
from B
group by Column1
while (@@rowcount>0)
update #x set
#x.Col2List = #x.Col2List +','+ (select min(Column2) from B where B.Column1=#x.Column1 and #x.Column2<B.Column2)
,
#x.Column2=(select min(Column2) from B where B.Column1=#x.Column1 and #x.Column2<B.Column2)
from B where B.Column1=#x.Column1 and #x.Column2<B.Column2
select Column1, Col2list from #x
go
-
I got this working since I have a SP that is already walking down the entries in table 1.
SET @RefDes = ''
select @RefDes = @RefDes + ref_des + ', ' from A
right outer JOIN
B ON A.job = B.job AND A.suffix = B.suffix AND A.oper_num = B.oper_num AND A.sequence = B.sequence where A.job = @JOB and B.item = @part
order by ref_des
I can't use temp tables b/c I'm calling it with BCP. The only problem I have now is that my list @RefDes will have duplicates in it since there are duplicates in the table A
I can't use 'select distinct' in front of my statement since it will only returns the last item in the list then
for instance I have "1,2,3,1,5,4,2" using distinct it returns only "2,"
I guess I need to write a function now that I pass a list to and pulls out the dups?
-
Check out this split code, just make a function out of it. Just do select Distinct EachId from dbo.fnSlip('1,2,3', ',')
Declare @vcDelimiter varchar(3)
Declare @IDs varchar(8000)
SET @IDs = '31,5,7,9,12'
SET @vcDelimiter = ','
Select dtSplitted.EachID, Rank from (
SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter),
CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter)) - N.PkNumber - len(@vcDelimiter)) as EachID
, (LEN(SUBSTRING(@IDs, 1, N.PkNumber)) - LEN (REPLACE (SUBSTRING(@IDs, 1, N.PkNumber), ',', ''))) + 1 AS Rank
FROM dbo.Numbers N
WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber, len(@vcDelimiter)) = @vcDelimiter
AND PkNumber 0
-
Forgot to include this part :
--I use this table for many other string operations as well
CREATE TABLE [Numbers] (
[PkNumber] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [Pk_Number] PRIMARY KEY CLUSTERED
(
[PkNumber]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Declare @i as int
set @i = 0
while @i < 8000
begin
Insert into dbo.Numbers Default values
set @i = @i + 1
end
GO
-
SET @RefDes = ''
select @RefDes = @RefDes +
CASE WHEN CHARINDEX(','+ref_des+', ',','+@RefDes)>0 THEN '' ELSE ref_des + ', ' END
from A
right outer JOIN
B ON A.job = B.job AND A.suffix = B.suffix
AND A.oper_num = B.oper_num AND A.sequence = B.sequence
where A.job = @JOB and B.item = @part
order by ref_des
Far away is close at hand in the images of elsewhere.
Anon. -
Have you read this ?? Charindex is the slowest solution for this type of problems :
-
Any string manipulation is slow and it is all relative to the size of the dataset
therefore another alternative
SET @RefDes = ''
select @RefDes = @RefDes + ref_des + ', '
from (
select distinct ref_des
from A
right outer JOIN
B ON A.job = B.job AND A.suffix = B.suffix
AND A.oper_num = B.oper_num AND A.sequence = B.sequence
where A.job = @JOB and B.item = @part
) x
order by ref_des
Far away is close at hand in the images of elsewhere.
Anon. -
My bad, I thaught you were writting the split code...
-
np, sets a challenge to rethink the solution, always a good idea
Far away is close at hand in the images of elsewhere.
Anon. -
How about finding 3 other ways then
. -
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks guys,
I used David's last approach. Simple and easy and response time is super fast for my record set.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply