How can I create a SQL table variable using records in another table as column names

Question

I've got the following SQL to create a table:

declare @FirstColumnSPResultTable       table
(
    [HotelId]               int,
    [HotelName]             nvarchar(256),
    [2016 - Period 1]       decimal(10,1),
    [2016 - Period 2]       decimal(10,1),
    [2016 - Period 3]       decimal(10,1),
    [2016 - Period 4]       decimal(10,1),
    [Overall]               decimal(10,1),
    [#Jobs]                 int,
    [Rank]                  int,
    [OverallRow]            bit
)

Where I have the [2016 - Period 1] to [2016 - Period 4] columns I would like to generate these based on a comma separated list of Period IDs that are passed into the Stored Procedure.

The PeriodIDs link to a Period table which contains ID and PeriodName.

I thought about creating the table with only the HotelId and HotelName then looping through the periods and creating each column using a WHILE loop and ALTER TABLE statement, somehow using it's name retrieved in a SELECT statement, then adding the last 4 columns to the table.

I think it is possible to do it this way but can anyone tell me a better way?

EDIT: Full Solution using dynamic sql as suggested by @ADyson:

DECLARE @Sql    nvarchar(max) = '

DECLARE @FirstColumnSPResultTable       table           ([HotelId]              int,
                                                         [HotelName]            nvarchar(256),'

DECLARE @FirstPeriodIdCommaDelimListAsColumns   nvarchar(max)
SET @FirstPeriodIdCommaDelimListAsColumns = STUFF
        (
            (
                SELECT      ',' + QUOTENAME(p.[PeriodName]) + ' decimal(10,1) '
                FROM        [dbo].[Period] p WITH(NOLOCK)
                WHERE p.PeriodId IN (SELECT Value FROM dbo.fn_Split(',', @FirstPeriodIdCommaDelimListInt))
                ORDER BY p.[Year], p.[PeriodName]
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,''
        ) + ','

SET @Sql = @Sql + @FirstPeriodIdCommaDelimListAsColumns + '
                                                         [Overall]              decimal(10,1),
                                                         [#Jobs]                int,
                                                         [Rank]                 int,
                                                         [OverallRow]           bit)'

so I create the first part of the table, then use FirstPeriodIdCommaDelimListAsColumns to store the next columns created from the periods as required. Then I finish off the table.

Are the `PeriodsID`s retrievable from a `SELECT` statement? If so, use this previous [SO answer](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — BeaglesEnd, Jun 06 '16 at 15:58

score 2 · Accepted Answer · answered Jun 06 '16 at 15:38

2

If you want to create a new table from the values in the other table, then you can use dynamic SQL to do this using the field names as variables, and build up a string to execute using the sp_executesql stored procedure.

answered Jun 06 '16 at 15:38

ADyson

57,178
14
51
63

score 0 · Answer 2 · answered Jun 06 '16 at 15:29

0

What you're looking for is a pivot table. You can SELECT HotelId and HotelName (or whatever your key is) and PIVOT on PeriodID.

answered Jun 06 '16 at 15:29

jjjjjjjjjjj

417
8
28

How would I then use this to create a table variable? – Percy Jun 07 '16 at 09:19
@Rick There's a tutorial in the link. It'll create a column using each distinct value in PeriodID. – jjjjjjjjjjj Jun 07 '16 at 15:14

How can I create a SQL table variable using records in another table as column names

2 Answers2