Merging multiple rows into one row

Question

View MyView:

............. 
. Id | Value.
.............
.  1  |  A  .
.  2  |  B  .
.  3  |  C  .
.  4  |  D  .
.............

My query as following:

"Select From dbo.MyView Where Id > 1"

Real results:

.............
. Id | Value.
.............
.  2  |  B  .
.  3  |  C  .
.  4  |  D  .
.............

My wished or desired result:

"B, C, D"

I need one string(one row, one column) which merege all values of the column Values.

How can I do that? Any help will be greatly appreciated?

score 2 · Accepted Answer · answered Nov 26 '13 at 10:11

2

How about Scalar Valued Function:

Create FUNCTION [dbo].[GetResults] 
(
    @ID int
)
RETURNS varchar(200)
AS
BEGIN
    DECLARE @Result varchar(200)

    SELECT @Result = COALESCE(@Result + ', ','') + Value
    From
        (   
            SELECT  Value
            FROM       MyTable
            WHERE     (ID > @ID)

        ) UniqueValues

    RETURN @Result

END

answered Nov 26 '13 at 10:11

Milen

8,697
7
43
57

Thanks a lot it seem to be exactly what we need! how can we called from a nested query? can we do that for example (SELECT .... SELECT(. HERE CALL IT..)) – Bassam Alugili Nov 26 '13 at 10:14
just use `select... select(...., dbo.GetResults(ParameterHere) as Results, ....)` – Milen Nov 26 '13 at 10:18
The answer is correct but it not working for Views we have a view and not a table. – Bassam Alugili Nov 26 '13 at 10:35
This works fine for me when using a view: CREATE VIEW [MyTestView] AS SELECT [dbo].[GetResults](1) AS [ValueString]; SELECT * FROM [MyTestView]; User3805967, you may be implementing this wrong, can you post more code? – laylarenee Nov 26 '13 at 11:16

Sateesh Pagolu · Answer 2 · 2013-11-26T10:15:01.057

1

Here you go

SELECT ','+Value from MyTable for xml path('')

OR

declare @test varchar(100)
select @test = coalesce(@test+',','')+Value from MyTable
select @test

edited Nov 26 '13 at 10:15

answered Nov 26 '13 at 10:07

Sateesh Pagolu

9,282
2
30
48

dear It must done in the SQL-Server not in code the query must be modified thanks for help. – Bassam Alugili Nov 26 '13 at 10:08
Didn't get you. You dont want to achieve it using sql? – Sateesh Pagolu Nov 26 '13 at 10:10
no I want do do it with SQL something like this link: http://stackoverflow.com/questions/8005846/sql-server-combining-multiple-rows-into-one-row – Bassam Alugili Nov 26 '13 at 10:11
Your answer is also correct thanks but but it not working for Views we have a view and not a table. Scalar not working :( – Bassam Alugili Nov 26 '13 at 10:37

score 1 · Answer 3 · answered Nov 26 '13 at 19:17

1

For a View use the solution proposed by Milen, create the Scalar Valued Function, and then in the View just use "dbo.GetResults(ParameterHere) as Results"

answered Nov 26 '13 at 19:17

user3035996

11
1

Thanks a lot exactly what we need +1 for you but I will marke the milen Answer! – Bassam Alugili Nov 27 '13 at 09:00

Merging multiple rows into one row

3 Answers3