Combine Multiple Rows into One Row (One Column) when Using SQL

Question

How to combine multiple rows into one row:

Example:
ID   NAME    NUMBER
24   infill  20
25   test1   10
26   test2   30
27   test5   35
28   test3   40

SELECT 
      name,
      number 
FROM table1 WHERE table1.id IN (24,26,28)

They will have result as:

NAME    NUMBER
infill  20
test2   30
test3   40

How to modify SQL statement above: and I have one column name as I really want.

Service

infill 20,test2 30, test3 40

I did:

select name + " " + number as Service 
from table1
where table1.id in (24,26,28)

Result is NULL, thank you all for reply my question.

possible duplicate of [Simulating group\_concat MySQL function in SQL Server?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) — ntalbs, Feb 10 '15 at 03:51
try to convert the `number` to `nvarchar(max)` and put `single quotes` to concat — Ameya Deshpande, Feb 10 '15 at 04:00
Also possible duplicate of [Convert multiple rows into one with comma as separator](http://stackoverflow.com/questions/887628/convert-multiple-rows-into-one-with-comma-as-separator) — Bill, Feb 10 '15 at 04:01

score 1 · Answer 1 · answered Feb 10 '15 at 04:11

1

You are close, the reason concat is not working since you aretrying to concat varchar with Int.

In order to concat, both the columns have to be of same datatype, try like this:

SELECT name + ' ' + Convert(nvarchar(max), number) AS Service 
FROM table1
WHERE table1.id IN (24,26,28)

answered Feb 10 '15 at 04:11

Paresh J

2,401
3
24
31

Thanks Paresh , result gives 3 rows as original. Problem is still there. – MINH TRAN Feb 10 '15 at 04:58

Pupa Rebbe · Answer 2 · 2015-02-10T04:50:32.557

Here is it

DECLARE @Str Varchar(max) = ''
SELECT @Str = @Str + Name + ' ' + CAST(Number As varchar) + ', ' AS Service FROM table1
WHERE table1.id in (24,26,28)
SELECT SUBSTRING(@Str, 0, LEN(@Str) - 2) AS Result

UPDATE:

More Simple

DECLARE @Str Varchar(max) 
SELECT @Str = COALESCE(@Str + ', ','') + Name + ' ' + CAST(Number As varchar) AS Service
FROM table1
WHERE table1.id in (24,26,28)
SELECT @Str AS Result

StackUser · Accepted Answer · 2015-02-10T08:08:43.530

0

Try like this

  DECLARE @MyTable TABLE
  (
     ID     INT,
     NAME   VARCHAR(100),
     NUMBER INT
  )

INSERT INTO @MyTable
VALUES      (24,
             'infill',
             '20'),
            (25,
             'test1',
             '10'),
            (26,
             'test2',
             '30'),
            (27,
             'test5',
             '35'),
            (28,
             'test3',
             '40')

SELECT stuff((SELECT ',' + NAME + ' ' + CONVERT(VARCHAR(100), NUMBER)
              FROM   @MyTable
              WHERE  id IN ( 24, 26, 28 )
              FOR XML PATH('')), 1, 1, '') AS SERVICE

edited Feb 10 '15 at 08:08

answered Feb 10 '15 at 05:48

StackUser

5,370
2
24
44

Thanks, NewUser, It works, again thanks a lot, so I can go home now, this problem took me for more than 2 hours to solve this problem at work, my boss was not happy, but tomorrow he will be. – MINH TRAN Feb 10 '15 at 06:54
how to name column like 'SERVICE'?, result with (No column name), Thanks a lot NewUser – MINH TRAN Feb 10 '15 at 07:00
I have edited my code, Please check now. Please select my answer if it works for you. – StackUser Feb 10 '15 at 08:09
It works, thank you, have a good day, I start a new working day in Canberra (Australia) now. Have a good day all. – MINH TRAN Feb 10 '15 at 22:51

Combine Multiple Rows into One Row (One Column) when Using SQL

3 Answers3