创建从源
问题描述:
变化值的数目我有一个情况我需要值转换成一排源表显示为COLUMN_NAMES和创造在target.I这些列的动态case语句有这个下面的示例查询做那(1/2方式)。我的问题是,我可能会在源行2CODES或3CODES或5CODES以上的价值观和我的目标表结构(没有列)应该像明智的创建。创建从源
请帮我解决任何用于动态创建基于源值无列。我曾尝试下面的情况下语句,并试图看到的,我们可以使用最大/最小功能上ROW_NUMBER来解决这个问题(只是盲目的思想)
请指点。
如:
源表SSS
NAME ID CODE
AAA 1 XXX
BBB 2 YYY
CCC 3 PPP
AAA 1 YYY
AAA 1 PPP
目标:TGT
NAME ID XXX YYY PPP
AAA 1 A A A
CCC 3 N N A
BBB 2 N A N
从单排多个值转换为多列对于给定的ID
这是什么我试过了:
insert into fat
select id,
max(case when rownum = 1 then val else '' end),
max(case when rownum = 2 then val else '' end),
max(case when rownum = 3 then val else '' end),
max(case when rownum = 4 then val else '' end)
from long_ordered
group by id
select * from fat
select id, va11 as val from fat
union all select id, va12 from fat
union all select id, va13 from fat
union all select id, va14 from fat
答
要做到这一点,你需要使用动态SQL和光标。请参阅下面的完整解释示例。
请注意,我用的动态SQL基于什么在SSS表创建TGT表。然后使用游标遍历SSS表,并为SSS中的每个记录更新TGT表。更新只为'A'条目完成,其他所有设置都使用默认设置为'N'。
create table SSS(Name varchar(3), ID int, Code varchar(3))
insert into SSS values
('AAA' , 1 , 'XXX'),
('BBB' , 2 , 'YYY'),
('CCC' , 3 , 'PPP'),
('AAA' , 1 , 'YYY'),
('AAA' , 1 , 'PPP');
--get a list of columns based on code field
declare @columns nvarchar(max) = (select distinct N'[' + code + N'] char(1) null default ''N'',' from sss for xml path(''));
--remove trailing comma
set @columns = LEFT(@columns, LEN(@Columns) - 1);
--declare dynamic sql command variable
declare @cmd nvarchar(max);
--populate command with SQL to create a table
set @cmd = N'CREATE TABLE TGT(Name varchar(3) not null, ID int not null, ' + @columns + ');';
--show command (for troubleshooting only)
select @cmd;
--execute command to create a new table.
exec sp_executesql @cmd;
go
--prepare the table by inserting the names and ids
insert into tgt(name, id)
select distinct name, id
from sss;
go
--declare variables
declare @name varchar(3), @id int, @code varchar(3);
declare @cmd nvarchar(max);
--declare cursor to iterate through sss
declare cur cursor fast_forward for
select name, id, code
from sss;
open cur;
fetch next from cur into @name, @id, @code;
--main loop
while (@@FETCH_STATUS = 0)
BEGIN
set @cmd = 'UPDATE tgt SET ' + @code + ' = ''A'' WHERE [name] = ''' + @name + ''' and [id] = ' + cast(@id as varchar(5)) + ';';
exec sp_executesql @cmd;
fetch next from cur into @name, @id, @code;
END
close cur;
deallocate cur;
go
select * from tgt;