Sql Server pivot string results into columns I am trying to transpose a dataset of string values, into columns, depending on the value of an integer
example:
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')

My desired output would look like this




index0
index1




dog
plant


cat
tree


mouse
null




I tried doing a pivot, but a pivot requires a function (sum, max, average etc)
e.g.
WITH pivot_data AS
(
select rowindex,imgName from imgText
)
select [index0],[index1] 
from pivot_data
PIVOT ( max(imgName) for rowindex IN ([index0],[index1]) )  as p;

but my error is
Error converting data type nvarchar to int.

I assume because I am trying to aggregate a varchar
note: rowindex can ONLY be 0 and 1
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