SQL Server: Order cols in a row

Question

I want to swap the value of columns to have them sorted.

Table:

pid | category1 | category2 | category3
----+-----------+-----------+----------
 1  |    a      |    b      |
 2  |    b      |    a      |
 3  |    a      |    c      |    b

Result should be:

pid | category1 | category2 | category3
----+-----------+-----------+----------
 1  |    a      |    b      |
 2  |    a      |    b      |
 3  |    a      |    b      |    c

My approach was to select the columns to rows, order them in groups an return the new columns:

pid | category
----+---------
 1  |   a
 1  |   b
 2  |   b
 2  |   a
 3  |   a
 3  |   c
 3  |   b

I found the pivot function, but didn't really understand how to use it in this context.

Answer 1

you can pivot as below

select * from (
    select *, RowN = row_number() over(partition by pid order by Category) from Categories
    ) a
    pivot (max(category) for RowN in ([1],[2],[3])) p

Output as below:

+-----+-----------+-----------+-----------+
| Pid | Category1 | Category2 | Category3 |
+-----+-----------+-----------+-----------+
|   1 | a         | b         | NULL      |
|   2 | a         | b         | NULL      |
|   3 | a         | b         | c         |
+-----+-----------+-----------+-----------+

For dynamic list of columns you can use as below:

declare @cols1 varchar(max)
declare @cols2 varchar(max)
declare @query nvarchar(max)

--Row Numbers with tally
;with c1 as (
select * from ( values  (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) v(n) )
,c2 as (select n1.* from c1 n1, c1 n2, c1 n3, c1 n4) 
,RowNumbers as ( 
select top (select max(cnt) from (select cnt = count(*) from Categories group by pid ) a) convert(varchar(6), row_number() over (order by (select null))) as RowN 
from c2 n1, c2 n2
)
select  @cols1 = stuff((select ','+QuoteName(RowN) from RowNumbers group by RowN for xml path('')),1,1,''),
        @cols2 = stuff((select ',' + QuoteName(RowN) + ' as '+ QuoteName(concat('Category' , RowN)) from RowNumbers group by RowN for xml path('')),1,1,'')

select @cols1, @cols2

Set @query = ' Select Pid, '+ @cols2 +' from ( '
Set @query += '     select *, RowN = row_number() over(partition by pid order by Category) from Categories) a '
Set @query += ' pivot (max(category) for RowN in (' + @cols1 + ')) p'

--select @query
exec sp_executesql @query

Answer 2

Sean Lange is right about how you should correct your database schema.

Here is something to get you your results until then:

Using cross apply(values ...) to unpivot your data along with row_number() to reorder your category inside a common table expression; then repivoting that data with conditional aggregation:

;with cte as (
select 
    t.pid
  , u.category
  , rn = row_number() over (partition by t.pid order by u.category)
from t
  cross apply (values (category1),(category2),(category3)) u(category)
where nullif(u.category,'') is not null  
)
select 
    pid
  , category1 = max(case when rn = 1 then category end) 
  , category2 = max(case when rn = 2 then category end) 
  , category3 = max(case when rn = 3 then category end)
from cte
group by pid

rextester demo: http://rextester.com/GIG22558

returns:

+-----+-----------+-----------+-----------+
| pid | category1 | category2 | category3 |
+-----+-----------+-----------+-----------+
|   1 | a         | b         | NULL      |
|   2 | a         | b         | NULL      |
|   3 | a         | b         | c         |
+-----+-----------+-----------+-----------+