Splitting dynamically SQL columns into multiple columns based on a different column value

Question

I am trying to convert dynamically a table like this:

+----+---------+-------+
| ID | Subject | Users |
+----+---------+-------+
|  1 | Hi!     | Anna  |
|  2 | Hi!     | Peter |
|  3 | Try     | Jan   |
|  4 | Try     | Peter |
|  5 | Try     | Jan   |
|  6 | Problem | Anna  |
|  7 | Problem | José  |
|  8 | Test    | John  |
|  9 | Test    | John  |
| 10 | Hi!     | Anna  |
| 11 | Hi!     | José  |
| 12 | Hi!     | Anna  |
| 13 | Hi!     | Joe   |
+----+---------+-------+

Into something like that:

+----+---------+-------+-------+-------+-------+
| ID | Subject | User1 | User2 | User3 | User4 |
+----+---------+-------+-------+-------+-------+
| 1  | Hi!     | Anna  | Peter | José  | NULL  |
| 2  | Try     | Jan   | Peter | NULL  | NULL  |
| 3  | Problem | Anna  | José  | NULL  | NULL  |
| 4  | Test    | John  | NULL  | NULL  | NULL  |
+----+---------+-------+-------+-------+-------+

I have been reading the following links, but they are thought for splitting a column into a predefined number of columns:

Splitting SQL Columns into Multiple Columns Based on Specific Column Value

Split column into two columns based on type code in third column

I would need to split it dinamically depending on the content of the table.

Are you sure what you want is a dynamic number of columns? How are you going to further ingest this data? — MK_, Apr 16 '18 at 09:08
you can look into this: https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — Kamil Gnyszka, Apr 16 '18 at 09:28

Wei Lin · Accepted Answer · 2018-04-16T10:29:21.657

SQL:

--【Build Test Data】
create table #Tem_Table ([ID] int,[Subject] nvarchar(20),[Users] nvarchar(20));
insert into #Tem_Table ([ID],[Subject] ,[Users]) values 
    ('1','Hi!','Anna')
    ,('2','Hi!','Peter')  
    ,('3','Try','Jan')  
    ,('4','Try','Peter')  
    ,('5','Try','Jan')  
    ,('6','Problem','Anna')  
    ,('7','Problem','José')  
    ,('7','Test','John')  
    ,('9','Test','John') 
    ,('10','Hi! ','Anna')  
    ,('11','Hi! ','José')  
    ,('12','Hi! ','Anna')  
    ,('13','Hi! ','Joe')   
;

--STEP 1 distinct and ROW_NUMBER
with distinct_table as (
    select  [Subject],[Users]
    ,ROW_NUMBER() OVER (PARTITION BY [Subject] order by [Users]) [rank]
    from (
        select distinct [Subject],[Users] from #Tem_Table
    ) T00
)
--STEP 2 Group by row_count
,group_table as (
    select [Subject] 
    from distinct_table T
    group by [Subject]
)
--STEP 3 Use Left Join and Rank
select 
    T.[Subject],T1.[Users] as User1, T2.[Users] as User2 , T3.[Users] as User3, T4.[Users] as User4
from group_table T
left join distinct_table T1 on T.[Subject] = T1.[Subject] and T1.[rank] = 1
left join distinct_table T2 on T.[Subject] = T2.[Subject] and T2.[rank] = 2
left join distinct_table T3 on T.[Subject] = T3.[Subject] and T3.[rank] = 3
left join distinct_table T4 on T.[Subject] = T4.[Subject] and T4.[rank] = 4
order by [Subject];

result:

-------------------- -------------------- -------------------- -------------------- --------------------
Hi!                  Anna                 Joe                  José                 Peter
Problem              Anna                 José                 NULL                 NULL
Test                 John                 NULL                 NULL                 NULL
Try                  Jan                  Peter                NULL                 NULL

Update the Dynamic version :

--STEP 1 distinct and ROW_NUMBER
SELECT * into #distinct_table from (
    select  [Subject],[Users]
    ,ROW_NUMBER() OVER (PARTITION BY [Subject] order by [Users]) [rank]
    from (
        select distinct [Subject],[Users] from #Tem_Table
    ) T00    
)T;

--STEP 2 Group by row_count
SELECT * into #group_table from (
    select [Subject] ,count(1) [count]
    from #distinct_table T
    group by [Subject]  
)T;


--Use Exec
DECLARE @select_sql AS NVARCHAR(MAX) = ' select  T.[Subject] ',
@join_sql AS NVARCHAR(MAX) = ' from #group_table T ',
@max_count INT = (SELECT max([count]) FROM #group_table),
@temp_string NVARCHAR(5),
@temp_string_addone NVARCHAR(5)
;

DECLARE @index int = 0 ;
WHILE @index < @max_count
BEGIN
    sELECT @temp_string = Convert(nvarchar(10),@index);
   sELECT @temp_string_addone = Convert(nvarchar(10),@index+1);
   select  @select_sql = @select_sql + ' , T'+@temp_string_addone+'.[Users] as User'+@temp_string_addone+' '
   select  @join_sql = @join_sql + 'left join #distinct_table T'+@temp_string_addone+' on T.[Subject] = T'+@temp_string_addone+'.[Subject] and T'+@temp_string_addone+'.[rank] = '+@temp_string_addone+' ';
   SET @index = @index + 1;
END;

EXEC (@select_sql
+ @join_sql
+' order by [Subject]; ')
;

The dynamic version was the thing I was looking for. This is great, thanks!! — Sergikito, Apr 16 '18 at 13:48
The code you proposed is clear and easy to follow, and it makes everything in variable and temporal tables, which makes it easily portable. — Sergikito, Apr 16 '18 at 13:54

score 0 · Answer 2 · answered Apr 16 '18 at 09:27

CREATE TABLE mytable
    ([ID] int, [Subject] varchar(7), [Users] varchar(5))
;

INSERT INTO mytable
    ([ID], [Subject], [Users])
VALUES
    (1, 'Hi!', 'Anna'),
    (2, 'Hi!', 'Peter'),
    (3, 'Try', 'Jan'),
    (4, 'Try', 'Peter'),
    (5, 'Try', 'Jan'),
    (6, 'Problem', 'Anna'),
    (7, 'Problem', 'José'),
    (8, 'Test', 'John'),
    (9, 'Test', 'John'),
    (10, 'Hi!', 'Anna'),
    (11, 'Hi!', 'José'),
    (12, 'Hi!', 'Anna'),
    (13, 'Hi!', 'Joe')
;


select distinct subject,
(select  users from  (
select  distinct users from mytable where subject=m.subject) a order by users offset 0 rows fetch next 1 row only) user1,
(select  users from  (
select  distinct users from mytable where subject=m.subject) a order by users offset 1 rows fetch next 1 row only) user2,
(select  users from  (
select  distinct users from mytable where subject=m.subject) a order by users offset 2 rows fetch next 1 row only) user3,
(select  users from  (
select  distinct users from mytable where subject=m.subject) a order by users offset 3 rows fetch next 1 row only) user4

from mytable m

Rahul Richhariya · Answer 3 · 2018-04-16T14:52:19.610

you can use below dynamic query to get the result-

create table test_Raw(ID int ,Subject varchar(100), Users varchar(100))

insert into test_Raw
values (1,' Hi!','Anna'),
(2,' Hi!','Peter'),
(3,'Try','Jan'),
(4,'Try','Peter'),
(5,'Try','Jan'),
(6,'Problem','Anna'),
(7,'Problem','José'),
(8,'Test','John'),
(9,'Test','John'),
(10,' Hi!','Anna'),
(11,' Hi!','José'),
(12,' Hi!','Anna'),
(13,' Hi!','Joe')

--select * from test_Raw

select   dense_RANK() over( order by Subject) Ranking1,  dense_RANK()     over(partition by Subject order by users) Ranking2 , Subject , Users  
into test 
from test_Raw
group by Subject , Users
order by 3

declare @min int , @mx int , @Select nvarchar(max) , @from nvarchar(max) ,     @vmin varchar(3) 

select @min= 1 , @mx = MAX(Ranking2) , @Select=  'select ' , @from = ' from     test t1 ' , @vmin = '' from test


while  (@min<=@mx)
begin
    select @vmin = CAST(@min as varchar(3))
    select @Select = @Select +  CASE WHEN @min = 1 THEN  't1.Ranking1 as ID     , t1.Subject  , t1.Users AS User1 ' ELSE ',t' +@vmin+'.Users as User'+@vmin  END 
    select @from = @from  +  CASE WHEN @min = 1 THEN  '' ELSE ' left join     test t'+@vmin + ' on t1.Ranking1 = t' + @vmin + '.Ranking1 and t1.Ranking2 + ' +     cast (@min-1 as varchar(10)) + ' = t'+@vmin+'.Ranking2'  END 
    set @min = @min + 1
end 

select @Select = @Select +  @from + ' where  t1.Ranking2 = 1'

exec sp_executesql @Select

This approach seems to be what I wanted, but somehow it returns an error, because you are using a temporal table called #test2 but was not defined anywhere. That is why I chose the IT WeiHan answer. — Sergikito, Apr 16 '18 at 13:56

Splitting dynamically SQL columns into multiple columns based on a different column value

3 Answers3

SQL:

result:

Update the Dynamic version :