我正在尝试动态转换这样的表：
+----+---------+-------+
| 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 |
+----+---------+-------+
变成这样的东西：
+----+---------+-------+-------+-------+-------+
| 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 |
+----+---------+-------+-------+-------+-------+
我一直在阅读以下链接，但它们被认为是将一列拆分为预定义数量的列：
根据特定列值将 SQL 列拆分为多个列
根据第三列中的类型代码将列拆分为两列
我需要根据表格的内容动态拆分它。
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];
结果：
-------------------- -------------------- -------------------- -------------------- --------------------
Hi! Anna Joe José Peter
Problem Anna José NULL NULL
Test John NULL NULL NULL
Try Jan Peter NULL NULL
更新动态版本：
--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]; ')
;
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
您可以使用下面的动态查询来获取结果-
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