Dynamic SQL to Generate Column Names

Dynamic SQL to generate column names?

Having answered a lot of these over the years by generating dynamic pivot SQL from the metadata, have a look at these examples:

SQL Dynamic Pivot - how to order columns

SQL Server 2005 Pivot on Unknown Number of Columns

What SQL query or view will show "dynamic columns"

How do I Pivot on an XML column's attributes in T-SQL

How to apply the DRY principle to SQL Statements that Pivot Months

In your particular case (using the ANSI pivot instead of SQL Server 2005's PIVOT feature):

DECLARE @template AS varchar(max)
SET @template = 'SELECT 
SKU1
{COLUMN_LIST}
FROM
OrderDetailDeliveryReview
Group By
OrderShipToID,
DeliveryDate,
SKU1
'

DECLARE @column_list AS varchar(max)
SELECT @column_list = COALESCE(@column_list, ',') + 'SUM(Case When Sku2=' + CONVERT(varchar, Sku2) + ' Then Quantity Else 0 End) As [' + CONVERT(varchar, Sku2) + '],' 
FROM OrderDetailDeliveryReview
GROUP BY Sku2
ORDER BY Sku2

Set @column_list = Left(@column_list,Len(@column_list)-1)

SET @template = REPLACE(@template, '{COLUMN_LIST}', @column_list)

EXEC (@template)

Dynamically column names from select sql

Is this what you were looking for:

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(ColumnName) 
    from tempData
    group by ColumnName, name
FOR XML PATH(''), Type
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')

set @query = N'SELECT Name, ' + @cols + N' from 
 (
    select Name, value, ColumnName
    from tempData
) x
pivot 
(
    SUM(value)
    for ColumnName in (' + @cols + N')
) p '

exec sp_executesql @query;

Changing this in your fiddle return the rows as you need it.

Generate column name dynamically in sql server

You would need to dynamically build the SQL as a string then execute it. Something like this...

DECLARE @s_dt INT
DECLARE @query NVARCHAR(MAX)

SET @s_dt = (SELECT DATEPART(dd, s_dt) FROM TableName WHERE 1 = 1) 

SET @query = 'SELECT s_dt'
  + ', NULL as dt' + RIGHT('0' + CAST(@s_dt as VARCHAR), 2)
  + ', NULL as dt' + RIGHT('0' + CAST((@s_dt + 1) as VARCHAR), 2)
  + ', NULL as dt' + RIGHT('0' + CAST((@s_dt + 2) as VARCHAR), 2)
  + ', NULL as dt' + RIGHT('0' + CAST((@s_dt + 3) as VARCHAR), 2)
  + ' FROM TableName WHERE 1 = 1)

EXECUTE(@query)

You will need to replace WHERE 1 = 1 in two places above to select your data, also change TableName to the name of your table and it currently puts NULL as the dynamic column data, you probably want something else there.

To explain what it is doing:

SET @s_dt is selecting the date value from your table and returning only the day part as an INT.

SET @query is dynamically building your SELECT statement based on the day part (@s_dt).

Each line is taking @s_dt, adding 0, 1, 2, 3 etc, casting as VARCHAR, adding '0' to the left (so that it is at least 2 chars in length) then taking the right two chars (the '0' and RIGHT operation just ensure anything under 10 have a leading '0').

How do I use loop to generate column names dynamically?

This would be much easier to do by just copy/pasting the column names and changing them to be the correct one. However if you must do it this way, I do not advise using a loop at all. This method uses a tally table to generate the columns you want to select (in this example, columns 1 through 30, but that can be changed), then generates a dynamic SQL statement to execute against the SData table:

Declare @From   Int = 1,
        @To     Int = 30,
        @Sql    NVarchar (Max)

Declare @Columns Table (Col Varchar (255))

;With Nums As
(
    Select  *
    From    (Values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) As V(N)
), Tally As
(
    Select  Row_Number() Over (Order By (Select Null)) As N
    From        Nums    A   --10
    Cross Join  Nums    B   --100
    Cross Join  Nums    C   --1000
)
Insert  @Columns
Select  'TRx' + Cast(N As Varchar)
From    Tally
Where   N Between @From And @To

;With Cols As
(
    Select  (
                Select  QuoteName(Col) + ',' As [text()]
                From    @Columns
                For Xml Path ('')
            ) As Cols
) 
Select  @Sql = 'Select ' + Left(Cols, Len(Cols) - 1) + ' From SData'
From    Cols

--Select    @Sql
Execute (@Sql)

Note: The --Select @Sql section is there to preview the generated query before executing it.

How to dynamically generate SQL query column names and values from arrays?

I assume your code examples are just pseudo code but I'll state the obvious just in case.

db.Query("insert into views (?,?,?,?,?,?) values (?,?,?,?,?,?)", colNames..., values...)

This is invalid Go since you can only "unpack" the last argument to a function, and also invalid MySQL since you cannot use placeholders (?) for column names.

db.Query("insert into views " + colNames + " values" + values)

This is also invalid Go since you cannot concatenate strings with slices.

---

You could fromat the slices into strings that look like this:

colNamesString := "(col1, col2, col3)"
valuesString   := "(val1, val2, val3)"

and now your second code example becomes valid Go and would compile but don't do this. If you do this your app becomes vulnerable to SQL injection and that's something you definitely don't want.

---

Instead do something like this:

// this can be a package level global and you'll need
// one for each table. Keep in mind that Go maps that
// are only read from are safe for concurrent use.
var validColNames = map[string]bool{
     "col1": true,
     "col2": true,
     "col3": true,
     // ...
}

// ...

var colNames, values []string
var phs string // placeholders for values

for k, v := range formData {
    // check that column is valid
    if !validColNames[k] {
        return ErrBadColName
    }
    colNames = append(colNames, k)
    values = append(values, v)
    phs += "?,"
}

if len(phs) > 0 {
    phs = phs[:len(phs)-1] // drop the last comma
}
phs = "(" + phs + ")"

colNamesString := "(" + strings.Join(colNames, ",") + ")"
query := "insert into views " + colNamesString + phs
db.Query(query, values...)

Applying a function to dynamic column names

You can do this with the help of INFORMATION_SCHEMA, STUFF and Dynamic SQL:

-- Get the all columns names from the underlying table
SELECT COLUMN_NAME
INTO #TEMP
FROM [Database_Name].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'T' AND COLUMN_NAME != 'ID'

DECLARE @COLUMNS  NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)

-- Construct a string with ISNULL
SELECT @COLUMNS =  STUFF((SELECT DISTINCT ',ISNULL(' + QUOTENAME(COLUMN_NAME) + ',0) ' + QUOTENAME(COLUMN_NAME) 
FROM #TEMP 
ORDER BY 1 
FOR XML PATH('')), 1, 1, '')

-- and use Dynamic SQL
SELECT @sql = 'SELECT ID,'+ @COLUMNS +' FROM T'

EXEC sp_executesql @sql

Automatically generate columns name in CTE using SQL Server

dynamic pivot doesn't work inside CTE

No, but a CTE works inside a dynamic query:

{assuming you have declared the variables used below}
SELECT @Cols = {query to get the column names in a comma-separated string}

SET @sql='
with CTE as 
(
    select 
        *
    from 
        (select 
             store, week, xCount
         from 
             table_1) src
    pivot
        (sum(xcount)
             for week in ('+@Cols+')
    ) piv;
)
Select * 
From CTE
'

EXEC (@sql)

Can i use recursive CTE?

No this isn't an appropriate use-case for a recursive CTE.

---

Related Topics