SQL Server: column to row

Question

我有一个表（table1），toypattern和additionalcolor有2列

：

表1

toypattern  additionalcolor
FL38        orange
FL38        yellow
LF66        grey
LF66        black

现在我需要编写一个查询来显示toypattern列、1st color列和2nd color列。 我需要显示多达 5 个额外的颜色选项

期望输出：

toypattern  1st color   2nd color     3rd color   4th color   5th color
  FL38        orange      yellow
  LF66        grey        black

如何编写这样的 SQL 查询？

Answer 1

试试这些：

;with cte as
(select toypattern, additionalcolor,
  row_number() over(partition by toypattern order by additionalcolor) As rn
from Table1)
select toypattern, 
  max(case when rn = 1 then additionalcolor end) as additionalcolor1,
  max(case when rn = 2 then additionalcolor end) as additionalcolor2
from cte
group by toypattern

这将返回：

toypattern  additionalcolor1    additionalcolor2
FL38        Orange              Yellow
LF66        Black               Grey

这样做的缺点是您需要添加新的 MAX（...） 线条以获得新的其他颜色。

//

//

另一种解决方案是将其他颜色作为CSV：

SELECT DISTINCT T1.toypattern,
STUFF((SELECT TOP 100 PERCENT ',' + s2.additionalcolor FROM Table1 AS s2 WHERE s2.toypattern = T1.toypattern ORDER BY ',' + s2.additionalcolor FOR XML PATH('')), 1, 1, '') AS Colours
FROM Table1 AS T1
ORDER BY T1.toypattern

这将返回：

toypattern  Colours
FL38    Orange,Yellow
LF66    Black,Grey

希望这有帮助。

Answer 2

好的，不确定这个问题是否与这个问题有关（http://stackoverflow.com/questions/11070509/sql-query-issue-on-left-join）：

但这里有一个解决方案，可能是你所追求的 - 获取了一些 03Usr 的代码（特别是row_number并添加了透视）：

;with cte as
(select toypattern, additionalcolor,
  rowno =  row_number() over(partition by toypattern order by additionalcolor)
from Table1)
SELECT
  ToyPattern
  --, ToyName
  , 'color 1' = [1]
  , 'color 2' = [2]
  , 'color 3' = [3]
  , 'color 4' = [4]
  , 'color 5' = [5]
FROM (  
  SELECT
  ToyPattern
  --, ToyName
  , rowno
  , additionalcolor
  FROM
    cte
) AS tac PIVOT (
  MAX (additionalcolor) FOR rowno in ([1],[2],[3],[4],[5])
) AS PivotTable

Answer 3

你用的是什么 Sql Server 版本？（我无法添加评论）像上面发布的Pivot是最好的，但是如果您使用的是旧的Sql Server版本（如2000），则可以使用CURSOR执行此操作：

drop table #temp
create table #temp
(
   toypattern varchar(20),
   additionalcolor1 varchar(20),
   additionalcolor2 varchar(20),
   additionalcolor3 varchar(20),
   additionalcolor4 varchar(20),
   additionalcolor5 varchar(20)
)
declare @toypattern as varchar(20)
declare @additionalcolor as varchar(20)     
declare @Counter integer 
declare @StrSql varchar(200)    
declare @LastToyPattern as varchar(20)
set @LastToyPattern = ''
declare MyCursor cursor for  select toypattern, additionalcolor from table1 order by toypattern
open MyCursor
fetch next from MyCursor into @toypattern, @additionalcolor 
   while @@fetch_status = 0     
   begin
      if (@LastToyPattern <> @toypattern)
      begin
         if (@LastToyPattern <> '') 
         begin
            set @StrSql = @StrSql + replicate(',null', 5 - @Counter)    
            exec(@StrSql)
         end
         set @Counter = 0
         set @StrSql = 'insert into #temp select ''' + @toypattern + ''''
      end
      set @StrSql = @StrSql + ', ''' + @additionalcolor + ''''  
      set @Counter = @Counter + 1
      set @LastToyPattern = @toypattern
      fetch next from MyCursor into @toypattern, @additionalcolor
   end
set @StrSql = @StrSql + replicate(',null', 5 - @Counter)
exec(@StrSql)
close MyCursor
deallocate MyCursor
select * from #temp

Answer 4

SELECT 
   first.toypattern,
   first.additionalColor,
   second.additionalColor
FROM
   Table1 first
   INNER JOIN Table2 second ON first.toypattern = second.toypattern
WHERE
   first.additionalColor < second.additionalColor

问题是，如果每个图案有两种以上的颜色，您将获得多个匹配项。

编辑：这并不能真正返回搜索超过 2 种颜色时可能想要的结果。 但是，鉴于此答案附带的评论，我不会删除它。