Sql Server 將字元串結果轉換為列

我正在嘗試將字元串值的數據集轉換為列，具體取決於整數的值

例子：

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 pivot_data AS
(
select rowindex,imgName from imgText
)
select [index0],[index1] 
from pivot_data
PIVOT ( max(imgName) for rowindex IN ([index0],[index1]) )  as p;

但我的錯誤是

Error converting data type nvarchar to int.

我假設是因為我正在嘗試聚合一個 varchar

注意：rowindex 只能是 0 和 1

您需要添加另一列，以便您為數據透視操作所擁有的聚合函式可以使用。試試下面的。

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 

引用自：https://dba.stackexchange.com/questions/289872