sql 2005 join results

HI,

Using Microsoft SQL Server 2005:

I have a table "test":

id, link_id, name

1 11 test1

2 11 test2

3 11 test3

4 12 test4

Is there a way to query this, and return the results grouped by "link_id", with the names joined?

EG,

SELECT link_id, name FROM test WHERE ???????

results:

link_id, name(s)

11 test1, test2, test3

12 test4

The blackbox way of doing this is with a CROSS APPLY and FOR XML PATH:

declare @t table (id int, link_id int, name varchar(max))
insert into @t select 1, 11, 'test1'
union all select 2, 11, 'test2'
union all select 3, 11, 'test3'
union all select 4, 12, 'test4'

select b.link_id, d.link_names
from (
    select distinct link_id
    from @t a
) b
cross apply (
    select name + ', ' as [text()]
    from @t c
    where b.link_id = c.link_id
    for xml path('')
) d (link_names)

For each row, a CROSS APPLY executes the applied subquery. In this case, the subquery is called twice, for link_id 11 and 12. The subquery then abuses the FOR XML operator to add the strings together.

If you run the query, it will print:

11   test1, test2, test3, 
12   test4, 

A recursive CTE example:

declare @t table (id int
                , link_id int
                , [name] varchar(10) )


insert @t
      select 1,11,'test1'
union select 2,11,'test2'
union select 3,11,'test3'
union select 4,12,'test4'

;with rnCTE
AS
(
        select * 
               ,ROW_NUMBER() OVER (PARTITION BY link_id
                                   ORDER BY  id
                                   ) AS rn
               ,ROW_NUMBER() OVER (PARTITION BY link_id
                                   ORDER BY  id desc
                                   ) AS rd
        from @t
)
,recCTE
AS
(       select link_id
               ,cast([name] as varchar(max)) as [name]
               ,rn
               ,rd
        from rnCTE
        where rn = 1

        UNION ALL

        select c.link_id
               ,c.[name] + ', ' + t.name as [name]
               ,t.rn
               ,t.rd
        from recCTE c
        join rnCTE  t
        on   t.link_id = c.link_id
        and  t.rn      = c.rn + 1
)
select * from recCTE 
where rd = 1 
order by link_id

Take a look at this article

http://www.sqlprof.com/blogs/sqldev/archive/2008/03/31/how-to-list-multiple-rows-of-data-on-same-line.aspx

Basically you will need to create a function which will return the test1, test2, test3 part. That is done by setting a variable. Combine that with a distinct list of link_id's and you have got your output.

You can use CTE's (Common Table Expressions) to accomplish this at the database level. They are a little tricky to work with at first but they can be used to solve some interesting problems. MSDN Link

In pure SQL, your best bet would be to write a scalar value function that would group all of your names, like so:

DECLARE @Names TABLE (id INT IDENTITY NOT NULL, name NVARCHAR(255))
INSERT INTO @Names SELECT name FROM test WHERE link_id = @link_id

DECLARE @ConcatString NVARCHAR(MAX)

WHILE EXISTS(SELECT NULL FROM @Names)
BEGIN
  SELECT @ConcatString = @ConcatString + ', ' + (SELECT TOP 1 name FROM @Names)
  DELETE FROM @Names WHERE id = (SELECT TOP 1 id FROM @Names)
END

RETURN @ConcatString

Then you can just do:

SELECT link_id, dbo.FunctionName(link_id)
FROM test
GROUP BY link_id

First create a function.

Create FUNCTION [dbo].[getname]

( -- Add the parameters for the function here @link_ID as int ) RETURNS varchar(1000) AS BEGIN -- Declare the return variable here DECLARE @names as varchar(1000)

-- Add the T-SQL statements to compute the return value here
SELECT @names = Coalesce(@names + ',', '') + Convert(varchar(1000), name)
from tblTableName
where link_ID = @link_ID

-- Return the result of the function
RETURN @names

END

then

select link_ID, GetName(link_ID) from tblOctHotelImages group by Link_ID, GetName(link_ID)

You will get the result.