Create varchar row from column in SQL

Question

I've got a function that returns a column from a table. Now I want to make this rows from the column I returned into a single row .

CREATE PROCEDURE [dbo].[creareStringContract]
    @numeTabela varchar(100),
    @numeColoana varchar(100),
    @valoareColana varchar(50),
    @numeColoanaValori varchar(100)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @str varchar(max) = ''

    -- Add the T-SQL statements to compute the return value here
    exec ('SELECT ' +@numeColoanaValori +'
    FROM ' +  @numeTabela + '
    WHERE  ' + @numeColoana +' = ' + @valoareColana )

    -- Return the result of the function
END
GO

CREATE TABLE #tmp 
(
    ID int  IDENTITY (0,1),
    IDContract int,
    Valoare decimal 
)

    INSERT INTO #tmp VALUES (1,-500)
    INSERT INTO #tmp VALUES (1,300)
    INSERT INTO #tmp VALUES (1,250)
    INSERT INTO #tmp VALUES (1,150)
    INSERT INTO #tmp VALUES (1,50)
    INSERT INTO #tmp VALUES (2,-900)
    INSERT INTO #tmp VALUES (2,350)
    INSERT INTO #tmp VALUES (2,300)
    INSERT INTO #tmp VALUES (2,250)
    INSERT INTO #tmp VALUES (2,200)
    INSERT INTO #tmp VALUES (2,100)
    INSERT INTO #tmp VALUES (3,-600)
    INSERT INTO #tmp VALUES (3,280)
    INSERT INTO #tmp VALUES (3,270)
    INSERT INTO #tmp VALUES (3,160)
    INSERT INTO #tmp VALUES (3,150)
    INSERT INTO #tmp VALUES (4,-1500)
    INSERT INTO #tmp VALUES (4,500)
    INSERT INTO #tmp VALUES (4,400)
    INSERT INTO #tmp VALUES (4,300)
    INSERT INTO #tmp VALUES (4,250)
    INSERT INTO #tmp VALUES (4,150)
    INSERT INTO #tmp VALUES (4,100)
    INSERT INTO #tmp VALUES (4,50)
    INSERT INTO #tmp VALUES (4,30)
    INSERT INTO #tmp VALUES (1,10)
    INSERT INTO #tmp VALUES (1,5)
    INSERT INTO #tmp VALUES (1,4)
    INSERT INTO #tmp VALUES (1,3)
    INSERT INTO #tmp VALUES (1,2)

Here is the sample data.

exec [creareStringContract] '#tmp','IDContract','1','Valoare'

Now this query result is .

Valoare
-500
300
250
150
50
10
5
4
3
2

Now I want this to be resulted as

IDContract  (No column name)
2              -500,300,250,150,50,10,5,4,3,2

That example has 3 columns returned, I only have one which is Valoare. — CiucaS, Jul 28 '14 at 10:52
Perhaps, but it can be modified quite easily to match your requirements. — DavidG, Jul 28 '14 at 10:55

score 1 · Accepted Answer · answered Jul 28 '14 at 10:52

1

Use following query in your function:

EXEC('SELECT 2 IDContract, 
    STUFF((Select '',''+' +@numeColoanaValori +'
    FROM ' +  @numeTabela + '
    WHERE  ' + @numeColoana +' = ' + @valoareColana+'
    For XML Path('''')),1,1,'''') AS ''(No column name)''')

answered Jul 28 '14 at 10:52

mehdi lotfi

11,194
18
82
128

Thank you, I had to do a cast in your answer but it works, and I get the desire result. Than you sir! – CiucaS Jul 28 '14 at 10:58
I've got an question... is this stuff ... xml path functions supported in SQL Server 2005? I found some vague information on google. – CiucaS Jul 28 '14 at 12:48

Create varchar row from column in SQL

1 Answers1