Sql Server pivot string results into columns

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags:

Leave a Reply