Need help on writing a SQL Server query

Question

There are two tables,

Table A

A_ID    A_Field  
1          blah1  
2          blah2  
3          blah3  
........

============================================================

Table B (A_ID is foreign key references Table A's A_ID)

A_ID  B_Field  
1      a   
1      b  
1      c  
2      a  
2      b  
2      c  

What is the BEST way to get result like below:

A_ID   A_Field   B_Field  
1        blah1       a,b,c  
2        blah2       a,b,c

Thanks a lot for the replies, they all works, however, there is one more request, "For XML" dows NOT work on SQL SERVER 2000, unfortunately my domain service's DB is SQL Server 2000, is there a simple query work on SQL SERVER 2000?? Thanks!

Answer 1

"Best" is a relative term. I would probably use multiple queries and concatenate the string in code.

You can create a scalar UDF that takes A_ID as a parameter, and builds the denormalized string from table B. Your final SELECT would be:

SELECT A.A_ID, A.A_Field, dbo.myUDF(A.A_ID)
FROM A

Answer 2

To get the results as you are asking you need to join two tables. Here is an example:

SELECT A_ID, A_FIELD, B_FIELD 
FROM Table A
LEFT JOIN Table B ON Table A.A_ID = Table B.A_ID

Answer 3

Try something like (* Full Example* )

DECLARE @TableA TABLE(
        A_ID INT,
        A_Field VARCHAR(20)
)
INSERT INTO @TableA SELECT 1,'blah1'
INSERT INTO @TableA SELECT 2,'blah2'
INSERT INTO @TableA SELECT 3,'blah3'

DECLARE @TableB TABLE(
        A_ID INT,
        B_Field VARCHAR(20)
)
INSERT INTO @TableB SELECT 1,'a'
INSERT INTO @TableB SELECT 1,'b'
INSERT INTO @TableB SELECT 1,'c'
INSERT INTO @TableB SELECT 2,'a'
INSERT INTO @TableB SELECT 2,'b'
INSERT INTO @TableB SELECT 2,'c'

;WITH Vals AS (
    SELECT  a.A_ID,
            a.A_Field,
            b.B_Field
    FROM    @TableA a INNER JOIN
            @TableB b ON a.A_ID = b.A_ID
)
SELECT  p1.A_ID,
        p1.A_Field
        ,STUFF( 
               (SELECT 
                    ', ' + p2.B_Field 
                    FROM Vals p2 
                    WHERE p2.A_ID=p1.A_ID 
                    ORDER BY p2.A_ID 
                    FOR XML PATH(''), TYPE 
               ).value('.','varchar(max)') 
               ,1,2, '' 
          ) AS B_Field 
FROM Vals p1 
GROUP BY    p1.A_ID,
            p1.A_Field

Output

A_ID    A_Field B_Field
1   blah1   a, b, c
2   blah2   a, b, c

Answer 4

You would be using a group by function to concatenate the column B_Field values and you can refer to this post on help on cancatenating the strings

Answer 5

I don't know if it is the best way, but it works:

declare @rv table (a_id int, a_field varchar(50), b_field varchar(50))
insert into @rv (a_id, a_field) select a_id, a_field from a

declare @id int
declare @b varchar(50)
declare cur cursor for select a_id from @rv
open cur
fetch next from cur into @id
while @@fetch_status = 0 begin
  set @b = ''
  select @b = @b + ',' + b_field from b where a_id = @id
  if len(@b) > 1 set @b = substring(@b, 2, len(@b)-1)
  update @rv set b_field = @b where a_id = @id
  fetch next from cur into @id
end
close cur
deallocate cur
select * from @rv

Answer 6

SELECT a.A_ID
     , a.A_Field
     , (SELECT CAST(b.B_Field+ ', ' AS VARCHAR(MAX)) 
         FROM table_B b
         WHERE (a.A_ID= b.A_ID) 
         FOR XML PATH ('')
      )AS whatever FROM table_a a

This should work as you want. Cheers. Try also this one

How to create a SQL Server function to "join" multiple rows from a subquery into a single delimited field?