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

Query

Hey

I have a table where i store answers for a questionare

i have the columns id(key), userId, QuestionId, answerID

QuestionID and answerID are related to different tables

one of the users didnt answer Question 6 and the rest of the users did. there is no empty entry for Question 6 so the database skips from Question 5 to Question 7

how do i write a query to find out the userID of the person that didnt answer Question 6

select userId from <questionaretable> where userid not in (select userid from <questionaretable> where questionid = 6)|||

Thx champion

Query

Hi,
I have a table with some product information. These products have related records in one more table with the manufacturing partno.
Each product has multiple manufacturing part no.
I want the product information and the manufacturing part no information
in the same line. If the manufacturing part no is one, then beside all the product info, the query should add one column and display the manufacturing no. And if the product has 2 manufacturing part no, then it should add 2 columns each having the manufacturing part no respectively.
I mean to say that there should be only one record for each product and beside the product info, it should have one/multiple fields showing the manufacturing model no.
Can anyone help me out how to do this query.
Thanks.You should do this on client part, not on server.|||Adding fields on the fly is not a good idea. What if there is an error in data entry or else where in the process of assigning manufacturer's part numbers to a product? Imagine the erroneous assignment resulted in 1500 part numbers associated with the same product, instead of 750 different ones? A better way would be to select distinct products, and in one (and only one) additional field have a comma-separated list of all associated manufacturer's part numbers, regardless of their number (well, preferrably where the numbers and the commas would fit into 8000-character field). For this to work all you need is a multi-statement function that returns varchar(8000) and takes product identifier as a parameter, so that it can be used in the same SELECT where you're retrieving distinct products.|||how do i start the function, unable to get any idea.

should i use a cursor or what?|||Suppose you have ProductID, ProductName, and PartNumber fields in your table (ProductsParts). For every PartNumber the values of ProductID and ProductName are repeated, right?


create function dbo.fn_PartNumbers (
@.ProductID int) returns varchar(8000) as
begin
declare @.PartNumbers varchar(8000)
set @.PartNumbers = ''
select @.PartNumbers = @.PartNumbers + PartNumber + ', '
from ProductsParts where ProductID = @.ProductID
return (substring(@.PartNumbers, 1, datalength(rtrim(@.PartNumbers))-1))
end
And then, here's your query:

select distinct ProductID, ProductName, PartNumbers = dbo.fn_PartNumbers(ProductID)
from ProductsParts|||Thank You very much. It worked out very well.
Thanks Again.