Technical Article

Display multiple rows values as single row value

,

As in the the query if there is the table named #Table1 and have column FirstName.

Lets say there are 4 records in table having specified record in a script query.

If you want to those 4 rows value separated by any delimeter, in my example it is ' ; '

and assign into any variable, here variable is @listValues .

and do the following query:

 

SELECT @listValues = ISNULL(
@listValues + @delimeter + CAST(FirstName AS VARCHAR(8000)),
CAST(FirstName AS VARCHAR(8000))
)
FROM #Table1
ORDER BY FirstName

 

and then if you run the query

 

SELECT list = @listValues

 

you will get the result:

John ; Michiel ; Peter ; Smith

 

SET NOCOUNT ON  
DECLARE @listValues VARCHAR(8000)  
DECLARE @delimeter VARCHAR(3)  
SET @delimeter = ' ; ' 
 
CREATE TABLE #Table1 (FirstName varchar(10))  
INSERT #Table1 VALUES ('Michiel')  
INSERT #Table1 VALUES ('John')  
INSERT #Table1 VALUES ('Smith')  
INSERT #Table1 VALUES ('Peter')  
 
SELECT @listValues = ISNULL(  
        @listValues + @delimeter + CAST(FirstName AS VARCHAR(8000)),  
        CAST(FirstName AS VARCHAR(8000))  
    )  
    FROM #Table1 
    ORDER BY FirstName  
 
DROP TABLE #Table1 
 
SELECT list = @listValues

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.42 (12)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.42 (12)

You rated this post out of 5. Change rating

Related content