How to combine multiple rows into one row for a field in MS SQL 2008 R2 without using temp table or variable?

Question

How do I accomplish my goal without using temp table or variable?

Table:

ID    ModelNum   Qty
123   ABC         4
123   DEF         4

Expected Result:

ID    Models       Qty
123   ABC | DEF    4

Thanks in advance!

possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Martin Smith, Nov 27 '13 at 18:59

score 1 · Accepted Answer · answered Nov 27 '13 at 19:34

DECLARE @T TABLE (ID INT,ModelNum CHAR(3),Qty INT)

INSERT INTO @T 
VALUES
(123,'ABC',4),
(123,'DEF',4),
(123,'GLK',4)

SELECT DISTINCT ID, STUFF(C.List, 1, 2, '') Models, Qty
FROM @T t
       CROSS APPLY (
                    SELECT '| ' + ModelNum
                    FROM @T
                    WHERE ID = t.ID
                    FOR XML PATH('')
                    )C(List)

Result Set

ID  Models          Qty
123 ABC| DEF| GLK   4

score 0 · Answer 2 · edited Mar 31 '14 at 04:22

Hi how about this Query below:

I have did the same example with some different logic and different attribute.

I can get the expected OP, please response if you have any suggestions for me on btechit@hotmail.com.

Declare:

@ConcatTable table (Ename varchar(30), Empno int)

Insert into @ConcatTable values ('Steve', 100),('mathew', 100),('jon', 101),('tom', 101)

--select * from @ConcatTable

--select ROW_NUMBER()over(order by Empno)Row2,* from    
--(select distinct Empno from @ConcatTable)C

declare @p varchar(100) = ''

select @p = @p+ ' '+Ename from (
select DENSE_RANK()over(order by Empno)as dens, * from @ConcatTable )A
where A.dens = 1

declare @q varchar(100) = ''

select @q = @q+ ' '+Ename from (
select DENSE_RANK()over(order by Empno)as dens, * from @ConcatTable )A
where A.dens = 2

--SELECT @p
--SELECT @q

declare @M table (Name varchar(30))

insert into @M
select * from(
select @p as v
union all
select @q as vv
)G


--SELECT ROW_NUMBER()over (order by Name desc)Rown1,* from @M

SELECT A.Name,CC.Empno FROM(
SELECT ROW_NUMBER()over (order by Name desc)Rown1,* FROM @M)A
inner join
(select ROW_NUMBER()over(order by Empno)Row2,* from
(select distinct Empno from @ConcatTable)C
)CC
on A.Rown1 = CC.Row2

How to combine multiple rows into one row for a field in MS SQL 2008 R2 without using temp table or variable?

2 Answers2