SQL Server - Multiple rows to columns by unique identifier

Question

I have the following database table:

|  Article   |   Material  |
|------------|-------------|
| article001 | material001 |
| article001 | material002 |
| article002 | material002 |
| article002 | material003 |
| article002 | material004 |
| article003 | material002 |
| article004 | material003 |
| article004 | material004 |
| article004 | material005 |
| article004 | material006 |

I want to achive a result like this:

|  Article   |  Material1  |  Material2  |  Material3  |  Material4  |
|------------|-------------|-------------|-------------|-------------|
| article001 | material001 | material002 |             |             |
| article002 | material002 | material003 | material004 |             |
| article003 | material002 |             |             |             |
| article004 | material003 | material004 | material005 | material006 |

I have already tried various possibilities (Pivot, CTE, Temp Tables). Unfortunately I do not get it. I'm pretty new to SQL.

Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — TheGameiswar, Jan 18 '17 at 15:11

John Cappelletti · Accepted Answer · 2017-01-19T10:51:22.110

1

A simple solution is a conditional aggregation (if you have a max number of materials). Let me know if you need to dynamic.

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

Returns

EDIT - Dynamic Pivot

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);

edited Jan 19 '17 at 10:51

answered Jan 18 '17 at 15:16

John Cappelletti

79,615
7
44
66

I don't have a max number of materials. A dynamic solution would be great. – Hellenbeck Jan 18 '17 at 15:19
@Hellenbeck give me a moment – John Cappelletti Jan 18 '17 at 15:37
I'am using SQL Server 2008 R2. Unfortunately there is no concat function. – Hellenbeck Jan 19 '17 at 06:59
@Hellenbeck Updated for 2008. Be sure to specify/tag 2008 in your future questions, otherwise we assume currently supported 2012+ (not extended) – John Cappelletti Jan 19 '17 at 10:55
Thank you very much. That solved my problem. In the future i will use the 2008 tag. – Hellenbeck Jan 19 '17 at 14:02

SQL Server - Multiple rows to columns by unique identifier

1 Answers1