SQL Server -通过唯一标识符将多行转换为列
SQL Server -通过唯一标识符将多行转换为列
提问于 2017-01-18 23:08:20
我有以下数据库表：
| Article | Material |
|------------|-------------|
| article001 | material001 |
| article001 | material002 |
| article002 | material002 |
| article002 | material003 |
| article002 | material004 |
| article003 | material002 |
| article004 | material003 |
| article004 | material004 |
| article004 | material005 |
| article004 | material006 |我想要实现这样的结果：
| Article | Material1 | Material2 | Material3 | Material4 |
|------------|-------------|-------------|-------------|-------------|
| article001 | material001 | material002 | | |
| article002 | material002 | material003 | material004 | |
| article003 | material002 | | | |
| article004 | material003 | material004 | material005 | material006 |我已经尝试了各种可能性(透视、CTE、临时表)。不幸的是，我不明白。我是SQL的新手。
回答 1
Stack Overflow用户
回答已采纳
发布于 2017-01-18 23:16:58
一个简单的解决方案是条件聚合(如果您有最大数量的材质)。如果你需要动态化，请告诉我。
Select Article
,Material1 = max(case when RN=1 then Material else '' end)
,Material2 = max(case when RN=2 then Material else '' end)
,Material3 = max(case when RN=3 then Material else '' end)
,Material4 = max(case when RN=4 then Material else '' end)
From (
Select *
,RN = Row_Number() over (Partition By Article Order by Material)
From YourTable
) A
Group By Article返回
编辑-动态透视
Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName('Material'+cast(RN as varchar(25))) From (Select Distinct RN = Row_Number() over (Partition By Article Order by Material) From Yourtable) A Order by 1 For XML Path('')),1,1,'')
Select @SQL = '
Select [Article],' + @SQL + '
From (
Select Article,Material
,Item = ''Material''+cast(Row_Number() over (Partition By Article Order by Material) as varchar(25))
From YourTable
) A
Pivot (max(Material) For [Item] in (' + @SQL + ') ) p'
Exec(@SQL);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/41722830
复制相关文章
相似问题
腾讯云开发者
