Query - concatenating across related records

Here's the setup:
Table A
--
ID........Code
A............X
B............X
C............Y
Table B
--
Code......Desc_Line......Desc
X.............01.........This is a description
X.............02.........for Code X
Y.............01.........Code Y is one line
I'd like the query to return:
A...X...This is a description for Code X
B...X...This is a description for Code X
C...Y...Code Y is one line
Question: How can I concatenate the multiple description lines in table B
to display on a single line with the corresponding data from Table A? I'd
like something easily re-usable (e.g. User Defined Function), but I'm not
sure how to go about it. Any help/suggestions would be greatly appreciated.SELECT A.id, B.code,
MAX(CASE WHEN desc_line = '01' THEN [desc] ELSE '' END)+
MAX(CASE WHEN desc_line = '02' THEN [desc] ELSE ' ' END)+
MAX(CASE WHEN desc_line = '03' THEN [desc] ELSE ' ' END)+
..
FROM TableA AS A
JOIN TableB AS B
ON A.code = B.code
GROUP BY A.id, B.code
"desc" is a reserved word. Avoid using it as a column name.
David Portas
SQL Server MVP
--|||I change the field Desc to Descrip, since Desc is a key word (Abbreviation
for Descending)
Create Function dbo.GetDescription (@.Code Char(1))
Returns VarChar(300)
As
Begin
Declare @.Out VarChar(300) Set @.Out = ''
Declare @.Lin Integer Set @.Lin = 0
While Exists (Select * From TableB
Where Code = @.Code
And Desc_Line > @.Lin)
Begin
Select @.Lin = Min(Desc_Line)
From TableB
Where Code = @.Code
And Desc_Line > @.Lin
-- --
Select @.Out = @.Out + Descrip + ' '
From TB
Where Code = @.Code
And Desc_Line = @.Lin
End
Return RTrim(@.Out)
End
-- ----
You use it like this:
dbo.GetDescription('X')
as in:
Select dbo.GetDescription('X')
"bradm98" wrote:
> Here's the setup:
> Table A
> --
> ID........Code
> A............X
> B............X
> C............Y
> Table B
> --
> Code......Desc_Line......Desc
> X.............01.........This is a description
> X.............02.........for Code X
> Y.............01.........Code Y is one line
> I'd like the query to return:
> A...X...This is a description for Code X
> B...X...This is a description for Code X
> C...Y...Code Y is one line
>
> Question: How can I concatenate the multiple description lines in table B
> to display on a single line with the corresponding data from Table A? I'd
> like something easily re-usable (e.g. User Defined Function), but I'm not
> sure how to go about it. Any help/suggestions would be greatly appreciated.[/colo
r]|||OH, I missed one TB -> TableB ... Here's correctted...
Create Function dbo.GetDescription (@.Code Char(1))
Returns VarChar(300)
As
Begin
Declare @.Out VarChar(300) Set @.Out = ''
Declare @.Lin Integer Set @.Lin = 0
While Exists (Select * From TableB
Where Code = @.Code
And Desc_Line > @.Lin)
Begin
Select @.Lin = Min(Desc_Line)
From TableB
Where Code = @.Code
And Desc_Line > @.Lin
-- --
Select @.Out = @.Out + Descrip + ' '
From TableB
Where Code = @.Code
And Desc_Line = @.Lin
End
Return RTrim(@.Out)
End
"CBretana" wrote:
> I change the field Desc to Descrip, since Desc is a key word (Abbreviation
> for Descending)
> Create Function dbo.GetDescription (@.Code Char(1))
> Returns VarChar(300)
> As
> Begin
> Declare @.Out VarChar(300) Set @.Out = ''
> Declare @.Lin Integer Set @.Lin = 0
> While Exists (Select * From TableB
> Where Code = @.Code
> And Desc_Line > @.Lin)
> Begin
> Select @.Lin = Min(Desc_Line)
> From TableB
> Where Code = @.Code
> And Desc_Line > @.Lin
> -- --
> Select @.Out = @.Out + Descrip + ' '
> From TB
> Where Code = @.Code
> And Desc_Line = @.Lin
> End
> Return RTrim(@.Out)
> End
> -- ----
> You use it like this:
> dbo.GetDescription('X')
> as in:
> Select dbo.GetDescription('X')
>
> "bradm98" wrote:
>|||Thanks so much. David's query worked, but your function really hit the nail
on the head!
"CBretana" wrote:
> OH, I missed one TB -> TableB ... Here's correctted...
> Create Function dbo.GetDescription (@.Code Char(1))
> Returns VarChar(300)
> As
> Begin
> Declare @.Out VarChar(300) Set @.Out = ''
> Declare @.Lin Integer Set @.Lin = 0
> While Exists (Select * From TableB
> Where Code = @.Code
> And Desc_Line > @.Lin)
> Begin
> Select @.Lin = Min(Desc_Line)
> From TableB
> Where Code = @.Code
> And Desc_Line > @.Lin
> -- --
> Select @.Out = @.Out + Descrip + ' '
> From TableB
> Where Code = @.Code
> And Desc_Line = @.Lin
> End
> Return RTrim(@.Out)
> End
>
> "CBretana" wrote:
>|||Just be aware, That using UDFs which read data from database tables is
frowned upon because in SQL 7/ SQL2k, every time the UDF Runs, it is
recompiled... So if, or example, you run
Select ID, Code, dbo.GetDescription(Code)
From TableA
And TableA has a million rows, then you will be recompiling the UDF a
million times. It can be a real performance hog...
I'm hoping MS fixes this in SQL 2005...
UDFs are at their best when they do NOT access data from database tables
"bradm98" wrote:
> Thanks so much. David's query worked, but your function really hit the na
il
> on the head!
> "CBretana" wrote:
>sql