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

• mustaccio: You are missing a column to order by; rows in a relational table do not have an intrinsic order despite what you seem to believe. Inserting a dog and then a plant does not mean that plant will be returned after dog when queried.

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 

• Crudler: nicely done. thank you. makes sense actually, If i take your date (inside the WITH), and pop it into excel, I can pivot. mine wouldnt have worked, nothing to pivot on.....
• Pantea: Where did you use the GRP ?? I mean what did you need the GRP for?
• Jonathan Fite: I didn’t, I don’t need it in the output. Without a column to aggregate against, it does it against the entire set. So adding another column like GRP will give the output desired. Try it, comment out the GRP in my example and look at the output. Try with a static value for GRP and see how it changes the output.