Transpose rows to single column

I am using SQLServer. I have a query which gives the following result.

1 Apple
1 Banana
1 Orange

Is there anyway in SQL to traspose it to the following:

1 Apple Banana Orange

Solution 1:

SELECT id, names = STUFF((SELECT ' ' + name
    FROM dbo.[table] WHERE id = t.id
    FOR XML PATH(''), 
        TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, '')
FROM dbo.[table] AS t
-- WHERE id = 1
GROUP BY id;

Solution 2:

You didn't specify which DBMS, so I'm assuming Oracle:

SELECT id, LISTAGG(name, ' ') WITHIN GROUP (ORDER BY id)
FROM   your_table
GROUP BY id;

or

SELECT id, wm_concat(name)
FROM   your_table
GROUP BY id;

Solution 3:

In SQL SERVER 2005 you can use xml, something like this

SELECT
  [Name] + ' ' as [text()] 
FROM fruits 
ORDER BY [Name] 
FOR XML path('')

for example

WITH fruits ([No],[Name]) AS (
SELECT
    1 , 'Apple1'  
UNION SELECT
    1, 'Banana1'
UNION SELECT
    1, 'Orange1'

UNION SELECT
    2, 'Apple2'
UNION SELECT
    2, 'Banana2'
UNION SELECT
    2, 'Orange2')

SELECT 
    [Name] + ' ' as [text()] 
FROM (SELECT [No], [Name] FROM fruits ) as fr 
ORDER BY [Name] 
FOR XML PATH('')

GO

Or if you like a more general example:

 WITH fruits ([No],[Name]) AS (
SELECT
    1 , 'Apple1'  
UNION SELECT
    1, 'Banana1'
UNION SELECT
    1, 'Orange1'

UNION SELECT
    2, 'Apple2'
UNION SELECT
    2, 'Banana2'
UNION SELECT
    2, 'Orange2')

SELECT 
    ft.[No], 
    (select [Name] + ' ' as [text()] from (SELECT [No], [Name] FROM fruits ) as fr where fr.[No] = ft.[No] ORDER BY [Name] for xml path('') ) as [Names]
 FROM 
    fruits ft
 GROUP BY [No]

Note: Remember to drop the last space in [Names]

Solution 4:

MSSQL for known values of fruit

with T(id, fruity) as (
   select 1, 'Apple'  union
   select 1, 'Banana' union
   select 1, 'Orange' union
   select 2, 'Apple'  union
   select 2, 'Banana' union
   select 3, 'Orange' 
)
SELECT * FROM T
PIVOT (
    MAX(fruity) 
    FOR fruity in ([Apple],[Banana],[Orange])
) pv

>>

id  Apple   Banana  Orange
1   Apple   Banana  Orange
2   Apple   Banana  NULL
3   NULL    NULL    Orange