Merging more than one table into one existing table

Question

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).

So, how can i do this?

Please help me.

Thanks.

Your question is probably a duplicate, but it's too unclear to help find it. — Tab Alleman, Apr 06 '18 at 13:05
I am just asking how can I merge the 12 different tables (which is returns by the while loop) into single one table without creating any new table. Here all the works done in while loop. — V.Deep, Apr 06 '18 at 13:09
The question is clearer now with sample data and desired output. It is a duplicate of this one: https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server — Tab Alleman, Apr 06 '18 at 13:32
Thanks for your reference but there were using the column name which is already in table record but Here I am giving the column name dynamically and here the column is not fixed its all depend loop. With static data ofcourse its easy but its dynamic. — V.Deep, Apr 06 '18 at 13:44
In that case you need to use dynamic sql. Both to create the table, and to create the INSERT. Check here: http://www.sommarskog.se/dynamic_sql.html#unknowncolumns — Tab Alleman, Apr 06 '18 at 13:52

score 2 · Accepted Answer · answered Apr 09 '18 at 10:04

Here the all column are created dynamically through loop

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)

Andomar · Answer 2 · 2018-04-06T13:37:45.377

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)

edited Apr 06 '18 at 13:37

answered Apr 06 '18 at 12:56

Andomar

232,371
49
380
404

Can't answer that without the the table structure and some example data – Andomar Apr 06 '18 at 13:15
1

look at my question again – V.Deep Apr 06 '18 at 13:29
Updated the answer. Note that you don't really need a table if your column name is equal to your column value :) – Andomar Apr 06 '18 at 13:39
1

Sorry i couldn't understand what are you want to say? Please clear it. – V.Deep Apr 06 '18 at 13:47

Merging more than one table into one existing table

2 Answers2