views:

39

answers:

2

My table TEST has the following rows:

test    | 1
test    | 2
test    | 3

How I query it to get the following result?

test    | 1 - 2 - 3
+1  A: 

You can use the Coalesce function to sort you numbers in a list. Hopefully this gives you a start:

Declare @T as Table (Col1 varchar(35), Col2 int)

Insert into @T(Col1, Col2)
Select 'Test', 1

Insert into @T(Col1, Col2)
Select 'Test', 2

Insert into @T(Col1, Col2)
Select 'Test', 3


DECLARE @X varchar(200)

SELECT @X = COALESCE(@X + ' - ', '') + Cast(Col2 as varchar(5))
From @T

Select @X
Craig Bart
Can you query in one line in sql server 2005 query?
monkey_boys
You would need to create a function to query the table for all col1 values. Then do something like this to use the function:Select Distinct dbo.Function_List(Col1)From testTable
Craig Bart
+1  A: 

Try:

SELECT x.column1,
       STUFF(SELECT ' - ' + t.column2
               FROM TEST t
              WHERE t.column1 = x.column1
           ORDER BY t.column1
            FOR XML PATH(''), 1, 1, '')
  FROM TEST x

Reference: STUFF

OMG Ponies
can u use this data for exampletest | 1test | 2test | 3
monkey_boys
what about FOR XML PATH('')
monkey_boys