CODEWITHSUNDEEP
sqlsql-serverselect
Adham
Adham

Reputation: 64904

How to select multiple rows as single row

In sql server, if I have a table like this :

id   |    name
-------------
1    |     x
2    |     y
3    |     z

I want to create a stored procedure to return the result in one row like this :

x | y | z

my code is :

alter PROCEDURE getSupervisorEvaluationPercentages
(
    @FirstSupervisorNumber nvarchar(6),
    @Year int
)
as
DECLARE @Count INT
DECLARE @OptionID INT
DECLARE @getOptionID CURSOR

declare @OptionCountTb table(c int)


SET @getOptionID = CURSOR FOR
    SELECT Id FROM ObjectiveOption
OPEN @getOptionID
FETCH NEXT
    FROM @getOptionID INTO @OptionID
    WHILE @@FETCH_STATUS = 0
    BEGIN
        select @Count = (select COUNT(Id) from EvaluationProcess 
            where FirstSupervisorNumber = @FirstSupervisorNumber and Year = @Year and FirstSupervisorEvaluation = @OptionID)
        insert into @OptionCountTb values(@Count)
        FETCH NEXT
        FROM @getOptionID INTO @OptionID
    END
CLOSE @getOptionID
DEALLOCATE @getOptionID

I want to display the values of the table @OptionCountTb as single row

Upvotes: 0

Views: 2184

Answers (4)

Joe G Joseph
Joe G Joseph

Reputation: 24106

try this solution:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + [name] 
                    from t01
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
 set @query = 'SELECT ' + @cols + ' from 
             (
                select [name]
                from t01
            ) x
            pivot 
            (
                count([name])
                for name in (' + @cols + ')
            ) p where 1=2 '
print(@query)
execute(@query)

Upvotes: 0

AnandPhadke
AnandPhadke

Reputation: 13506

CREATE TABLE t1(id int,name varchar(10))
INSERT INTO t1
VALUES(1,'x'),
(2,'y'),
(3,'z')

SELECT STUFF((select '|'+name from t1 for XML path('')),1,1,'')

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

PIVOT is one of the ways http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

Upvotes: 1

yogi
yogi

Reputation: 19619

select 
    name + ' '
from TableName
for xml path('')

Upvotes: 0

Related Questions