How to select multiple rows as single row

Question

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

check this answer stackoverflow.com/questions/2830139/…

Ammar Bukhari
– Ammar Bukhari

2012-07-25 09:59:57 +00:00
Commented Jul 25, 2012 at 9:59 — Ammar Bukhari
– Ammar Bukhari, Commented Jul 25, 2012 at 9:59

Madhivanan · Accepted Answer · 2012-07-25 09:52:44Z

1

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

answered Jul 25, 2012 at 9:52

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

yogi · Accepted Answer · 2012-07-25 09:46:42Z

0

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

answered Jul 25, 2012 at 9:46

yogi

19.6k13 gold badges67 silver badges93 bronze badges

1 Comment

Adham Over a year ago

but I want each value to appear as a separate cell

AnandPhadke · Accepted Answer · 2012-07-25 11:05:54Z

0

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,'')

answered Jul 25, 2012 at 11:05

AnandPhadke

13.5k5 gold badges28 silver badges34 bronze badges

Comments

Joe G Joseph · Accepted Answer · 2012-07-25 12:04:58Z

0

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)

answered Jul 25, 2012 at 12:04

Joe G Joseph

24.2k5 gold badges58 silver badges59 bronze badges

Collectives™ on Stack Overflow

How to select multiple rows as single row

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related