Is there a way to turn the results of a SELECT, FROM, GROUPBY statement into a new table?

Basically I need to put some tables from SQL into Excel to analyze it with a third-party program. However, when using Excel, the PivotTable date columns are in a specific format (d mm) and for the third-party program it will not accept it.

However, in SQL where I have done some aggregations on the raw data, the format that I have used for the aggregation statement is absolutely correct and I would like to turn those results into a separate table so that I can feed it to the external program. I will have an example of what I mean below.

This is the resulting view of what the SQL aggregation statement on the data looks like.

    Time             TADCount            TenantId
2019-11-22 00:00:00.000 1   4754F795-2FB9-4647-B28F-2CF2412F0BA2
2019-11-29 00:00:00.000 1   4754F795-2FB9-4647-B28F-2CF2412F0BA2
2019-11-30 00:00:00.000 1   4754F795-2FB9-4647-B28F-2CF2412F0BA2
2019-12-07 00:00:00.000 11  4754F795-2FB9-4647-B28F-2CF2412F0BA2
2019-12-08 00:00:00.000 2   4754F795-2FB9-4647-B28F-2CF2412F0BA2
2019-11-23 00:00:00.000 1   FD85A7E0-BDF5-4A6E-9B69-34AF1A518CB0
2019-11-27 00:00:00.000 1   FD85A7E0-BDF5-4A6E-9B69-34AF1A518CB0
2019-11-28 00:00:00.000 1   FD85A7E0-BDF5-4A6E-9B69-34AF1A518CB0
2019-12-02 00:00:00.000 1   FD85A7E0-BDF5-4A6E-9B69-34AF1A518CB0
2019-12-04 00:00:00.000 1   FD85A7E0-BDF5-4A6E-9B69-34AF1A518CB0
2019-12-06 00:00:00.000 3   FD85A7E0-BDF5-4A6E-9B69-34AF1A518CB0
2019-11-24 00:00:00.000 1   1CB49648-420B-454A-B39D-4DAB361C18B8
2019-11-24 00:00:00.000 5   0E84F35A-D7C1-4BDE-B805-6C7691B9667F
2019-11-26 00:00:00.000 1   0E84F35A-D7C1-4BDE-B805-6C7691B9667F
2019-12-05 00:00:00.000 1   0E84F35A-D7C1-4BDE-B805-6C7691B9667F
2019-12-07 00:00:00.000 2   0E84F35A-D7C1-4BDE-B805-6C7691B9667F

For reference, this is the aggregation statement that I have made.

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, [Time]), 0) AS Time
  , Count([TenantId]) AS TADCount,
  [TenantId]
FROM [dbo].[acms_data] 
GROUP by DATEADD(DAY, DATEDIFF(DAY, 0, [Time]), 0),[TenantId]

Basically I would like to turn that view above the code statement into a table with exactly those values and headers into a separate table that can be interacted with.

Answer

SELECT INTO is what you want to use here. Assuming you have the permissions to create a new table the steps would be:

Create the table you want to insert the data in
Convert the SELECT query into a SELECT INTO query.

Here’s an example from W3 Schools I’ve modified for your use case that should work. The main thing is that the new_table should already be created then modify your select query by adding an INTO:

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, [Time]), 0) AS Time
  , Count([TenantId]) AS TADCount,
  [TenantId]
INTO [dbo].[new_table]
FROM [dbo].[acms_data] 
GROUP by DATEADD(DAY, DATEDIFF(DAY, 0, [Time]), 0),[TenantId]

Advertisement

Answer