Create a new column for a table with select data from another table

Question

I have a SQL table that shows the date and time information of a topic.

FirstTable looks like this:

id	DateP	TimeP
1	1397/01/02	01:30
2	1398/05/09	05:30
3	1398/06/07	05:10
4	1398/08/09	06:12
5	1399/02/01	07:15

I want to create columns for another table that are the result of selecting the DateP column and the TimeP column from FirstTable.

SecondTable looks like this:

id	EmployeeID	1397/01/02_01:30	1398/05/09_05:30	1398/06/07_05:10	1398/08/09_06:12	1399/02/01_07:15

DECLARE @i INT = 0;
DECLARE @DatTim NVARCHAR(50)='';
SELECT @count=  Count(*) FROM FirstTable

WHILE @i <= @count
BEGIN
    @DatTim=(select DateP+TimeP FROM FirstTable where id=@i)   
    ALTER TABLE SecondTable ADD @DatTim NVARCHAR(50);
    SET @i = @i + 1;
END

Please guide me in finding the SQL code.

I think you search this https://stackoverflow.com/questions/16683758/how-to-create-a-table-from-select-query-result-in-sql-server-2008 — László Tóth, Sep 19 '21 at 17:50
Hi László Tóth, But I want the selected data from one table to be recorded in another table as the column name. — honey bee, Sep 20 '21 at 03:46

honey bee · Accepted Answer · 2021-12-22T09:49:12.477

0

DECLARE @i INT = 1;
DECLARE @count INT = 0;
DECLARE @DatTim NVARCHAR(50)='';
Set @count=(select Count(*) FROM FirstTable)

WHILE @i <= @count
BEGIN
    Set @DatTim=(select ISNULL(DateP,'') + '_'+ISNULL(TimeP,'') FROM FirstTable where id=@i)   
    exec ('ALTER TABLE SecondTable ADD ['+@DatTim +'] int NULL')
    SET @i = @i + 1;
END

edited Dec 22 '21 at 09:49

answered Sep 20 '21 at 05:30

honey bee

41
7

score 0 · Answer 2 · answered Sep 20 '21 at 11:21

These cycle row by row can be slow.

I think you should use pivot with create table.

https://www.linkedin.com/pulse/pivot-dynamic-columns-sql-server-rotate-table-data-ahmed-abdelhameed and this How to create a table from select query result in SQL Server 2008

Create a new column for a table with select data from another table

2 Answers2