将多个表合并到一个现有表中

This is the table creation and insertion query

这是表创建和插入查询

If not exists(select * from sysobjects where name='hrs')
Create table hrs(hr int)

declare @cnt int =1

while @cnt <= 12
begin
  insert into hrs values(@cnt)
  set @cnt=@cnt+1
end

The above code gives the output like

上面的代码给出了类似的输出

but I just want that

但我只想要那个

declare @cnt1 int = 1

while @cnt1<=12
begin
   EXEC('select he'+@cnt1+' = case when hr = 1 then '+@cnt1+' end from hrs')
   set @cnt1=@cnt1+1
end

The above code returns the 12 different table but i just want the all records in one table (without creating any new table).

上面的代码返回12个不同的表,但我只想要一个表中的所有记录(不创建任何新表)。

So, how can i do this?

那么,我该怎么做呢?

Please help me.

请帮帮我。

Thanks.

2 个解决方案

#1

Your query doesn't make a lot of sense, but you can build a list of columns and then exec that:

您的查询没有多大意义,但您可以构建列列表然后执行:

declare @columns nvarchar(max)
declare @cnt int = 1
while @cnt <= 12
    begin
    set @columns = isnull(@columns + ', ', '') + 'He' + cast(@cnt as nvarchar) + 
        ' = sum(case when hr = ' + cast(@cnt as nvarchar) + ' then hr end)'
    end
declare @sql nvarchar(max) = 'select ' + @columns ' + from hr'
exec (@sql)

#2

Here the all column are created dynamically through loop

这里all列是通过循环动态创建的

Here are the full query

这是完整的查询

declare @s varchar(MAX)=''
declare @j int = 1
while @j<=12
begin
if @j = 12
Set @s = @s+'he'+convert(varchar,@j)+'=MAX(case when hr='+convert(varchar,@j)+' then '+convert(varchar,@j)+' end)'
else
set @s = @s+'he'+convert(varchar,@j)+'=MAX(case when hr='+convert(varchar,@j)+' then '+convert(varchar,@j)+' end),'
set @j=@j+1
end
set @s = 'select '+@s+' from hrs'
exec(@s)

#1