Sql Server pivot string results into columns

Question

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

Answer 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