1

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

1
  • 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.
    – mustaccio
    Apr 15, 2021 at 15:49

1 Answer 1

2

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 
4
  • 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.....
    – Crudler
    Apr 16, 2021 at 6:01
  • Where did you use the GRP ?? I mean what did you need the GRP for?
    – Pantea
    Apr 18, 2021 at 11:55
  • 1
    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. Apr 18, 2021 at 11:58
  • Wow... SO professional . Thanks
    – Pantea
    Apr 18, 2021 at 12:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.