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_
– MK_, Commented Apr 16, 2018 at 9:08
you can look into this: stackoverflow.com/questions/15931607/… — Kamil Gnyszka
– Kamil Gnyszka, Commented Apr 16, 2018 at 9:28

Wei Lin · Accepted Answer · 2018-04-16 10:29:21Z

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!!
The code you proposed is clear and easy to follow, and it makes everything in variable and temporal tables, which makes it easily portable.

Kedar Limaye · Accepted Answer · 2018-04-16 09:27:36Z

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 · Accepted Answer · 2018-04-16 14:52:19Z

0

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

edited Apr 16, 2018 at 14:52

answered Apr 16, 2018 at 10:20

Rahul Richhariya

5243 silver badges11 bronze badges

1 Comment

Sergikito Over a year ago

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.

Collectives™ on Stack Overflow

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

3 Answers 3

SQL:

result:

Update the Dynamic version :

3 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

SQL:

result:

Update the Dynamic version :

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related