Sql Server pivot string results into columns
You need to add another column so that the aggregate function you have for the pivot operation has something to bite into.  Try the below.
DROP TABLE IF EXISTS #imgText

create table #imgText (
rowindex int,
imgName varchar(50)
)

insert into #imgText (rowindex,imgName) values (0,'dog')
insert into #imgText (rowindex,imgName) values (1,'plant')
insert into #imgText (rowindex,imgName) values (0,'cat')
insert into #imgText (rowindex,imgName) values (1,'tree')
insert into #imgText (rowindex,imgName) values (0,'mouse')

;WITH CTE_Raw AS
    (
    SELECT rowindex 
        , imgName 
        , GRP = ROW_NUMBER() OVER (PARTITION BY rowindex ORDER BY rowindex)
    FROM #imgText
    )
SELECT [0] AS index0
    , [1] AS index1
FROM CTE_Raw AS T
    PIVOT (MAX(imgName) FOR rowindex IN ([0], [1])) AS pvt