Looping through column names with dynamic SQL
You can use dynamic SQL and get all the column names for a table. Then build up the script:
Declare @sql varchar(max) = ''
declare @tablename as varchar(255) = 'test'
select @sql = @sql + 'select [' + c.name + '],count(*) as ''' + c.name + ''' from [' + t.name + '] group by [' + c.name + '] order by 2 desc; '
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id
where t.name = @tablename
EXEC (@sql)
Change @tablename to the name of your table (without the database or schema name).
How can I dynamically reference column names while looping through a query's results?
It's possible using structure notation, but also requires a query row number. The general syntax is
#queryName[ "columnName" ][ rowNum ]#
<!---
.. or specifically
--->
#getCapabilityAndDescription[ DescriptionVariable ][ 1 ]#
To output all of the query columns dynamically. Use GetMetaData() to retrieve an array of query column properties (in select order). Then use the name property to output each column value:
<!--- Demo query --->
<cfset yourQuery = queryNew("EntryID,Baker3Description"
, "integer,varchar"
, [[1,"Descrip A"],[2,"Descrip B"]]
)>
<cfset meta = getMetaData(yourQuery)>
<cfoutput query="yourQuery">
<cfloop array="#meta#" index="props">
#yourQuery[props.name][currentRow]#
</cfloop>
<br>
</cfoutput>
Though be careful building that kind of dynamic sql. If any of the values used to build the column names are user supplied (for example getJobDesc.Type) the query will be vulnerable to second order sql injection.
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.
Loop through Column Names in a table and do a set of operations - SQL Server 2008
You can use dynamic sql:
DECLARE @SQL varchar(max), @i INT
SET @i=2
while (@i<=22)
begin
/* Then cover all calculations with this one: */
SET @SQL='SELECT [DEF].[CONCATENATE], SUM(DEF.[C1_'+cast(@i as varchar)+'_PREV]) as
[C1_'+cast(@i as varchar)+'_prev]
INTO #TMP_C1_'+cast(@i as varchar)+'_CONCATENATE_PREV
FROM DEF
GROUP BY DEF.[CONCATENATE]
/* and all your code with the same trick in @i to the END */
'
--PRINT (@SQL) -- print it before use to see the result script
EXEC (@SQL)
/* Than do your iterations etc. */
set @i+=1
end
And don't forget to substitute all ' inside @SQL with ''.
Also you need to do all manipulations with temp tables inside @SQL, if you want to do final update outside the dynamic sql, just make tables real and then delete them.
[UPDATE]
As far as you faced with problem of altering temp tables, I tried to reproduce this error, but nothing happens, everything works fine. Please use this code as an example.
declare @sql varchar(max),@i int
set @i=2
while @i<=22
begin
set @sql='
select ID,Code into #TMP_C1_'+cast(@i as varchar)+'_CONCATENATE_PREV from (select 0 as ID, ''a'' as Code) t1
alter table #TMP_C1_'+cast(@i as varchar)+'_CONCATENATE_PREV add [Col1] varchar(255), [Col2] Varchar(255), [Col3] Money
select * from #TMP_C1_'+cast(@i as varchar)+'_CONCATENATE_PREV'
--print (@sql)
exec (@sql)
set @i+=1
end
First, I create temp table with dynamic name. Second, add new columns. The last is successful verifying. Did you execute all creations/alters in the same @sql-batch? If no, this won't work, because this tables are available only inside this batch (that's why we used varchar(max) when declared). Please describe your actions in details, maybe there is a mistake somewhere.
Iterate through columns and list all columns where a record has a value
Due to the OP's comment that they have 100's of columns, this suggests that they need a dynamic Solution. I finished this solution just as the OP commented that they are using 2016, so this will not work on 2016. They OP will need to convert this to the older FOR XML PATH and STUFF method instead of using STRING_AGG.
Other than that, this works:
USE Sandbox;
GO
CREATE TABLE dbo.YourTable (ID int,
[Group] char(3),
Col1 char(3),
Col2 char(3),
Col3 char(3),
Col4 char(3));
GO
INSERT INTO dbo.YourTable
VALUES(1,'AAA','foo','bar',NULL,NULL),
(2,'AAA','123','far',NULL,'baz'),
(3,'BBB',NULL,NULL,NULL,NULL),
(4,'CCC','345','123',NULL,NULL),
(5,'AAA',NULL,NULL,'czx',NULL);
GO
--Hard coded example, to get the idea correct first
WITH UnPvt AS(
SELECT DISTINCT
YT.[Group],
V.ColumnName
FROM dbo.YourTable YT
CROSS APPLY (VALUES(N'Col1',Col1),
(N'Col2',Col2),
(N'Col3',Col3),
(N'Col4',Col4))V(ColumnName,ColumnValue)
WHERE V.ColumnValue IS NOT NULL)
SELECT YT.[Group],
STRING_AGG(U.ColumnName,'; ') WITHIN GROUP (ORDER BY U.ColumnName) AS Cols
FROM (SELECT DISTINCT [Group] FROM dbo.YourTable) YT
LEFT JOIN UnPvt U ON YT.[Group] = U.[Group]
GROUP BY YT.[group]
GO
--Dynamic Solution
DECLARE @SchemaName sysname = N'dbo',
@TableName sysname = N'YourTable';
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
DECLARE @Delimiter nvarchar(50) = N',' + @CRLF + N' ';
SET @SQL = N'WITH UnPvt AS(' + @CRLF +
N' SELECT DISTINCT' + @CRLF +
N' YT.[Group],' + @CRLF +
N' V.ColumnName' + @CRLF +
N' FROM dbo.YourTable YT' + @CRLF +
N' CROSS APPLY (VALUES' +
(SELECT STRING_AGG(N'(N' + QUOTENAME(c.[name],'''') + N',' + QUOTENAME(c.[name]) + N')',@Delimiter) WITHIN GROUP (ORDER BY C.[name])
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN sys.columns c ON t.object_id = c.OBJECT_ID
WHERE s.[name] = @SchemaName
AND t.[name] = @TableName
AND C.[name] NOT IN (N'ID',N'Group')) + N')V(ColumnName,ColumnValue)' + @CRLF +
N' WHERE V.ColumnValue IS NOT NULL)' + @CRLF +
N'SELECT YT.[Group],' + @CRLF +
N' STRING_AGG(U.ColumnName,''; '') WITHIN GROUP (ORDER BY U.ColumnName) AS Cols' + @CRLF +
N'FROM (SELECT DISTINCT [Group] FROM dbo.YourTable) YT' + @CRLF +
N' LEFT JOIN UnPvt U ON YT.[Group] = U.[Group]' + @CRLF +
N'GROUP BY YT.[group];';
PRINT @SQL;
EXEC sp_executesql @SQL;
GO
DROP TABLE dbo.YourTable;
DB<>Fiddle
Note that this assumes that all columns (apart from ID and Group) have the same data type as well.
Edit: Sigh... FOR XML PATH solution:
DECLARE @SchemaName sysname = N'dbo',
@TableName sysname = N'YourTable';
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
DECLARE @Delimiter nvarchar(50) = N',' + @CRLF + N' ';
SET @SQL = N'WITH UnPvt AS(' + @CRLF +
N' SELECT DISTINCT' + @CRLF +
N' YT.[Group],' + @CRLF +
N' V.ColumnName' + @CRLF +
N' FROM dbo.YourTable YT' + @CRLF +
N' CROSS APPLY (VALUES' +
STUFF((SELECT @Delimiter + N'(N' + QUOTENAME(c.[name],'''') + N',' + QUOTENAME(c.[name]) + N')'
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN sys.columns c ON t.object_id = c.OBJECT_ID
WHERE s.[name] = @SchemaName
AND t.[name] = @TableName
AND C.[name] NOT IN (N'ID',N'Group')
ORDER BY c.[name]
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,DATALENGTH(@Delimiter)/2,N'') + N')V(ColumnName,ColumnValue)' + @CRLF +
N' WHERE V.ColumnValue IS NOT NULL)' + @CRLF +
N'SELECT YT.[Group],' + @CRLF +
N' STUFF((SELECT N''; '' + ColumnName' + @CRLF +
N' FROM UnPvt U' + @CRLF +
N' WHERE U.[Group] = YT.[Group]' + @CRLF +
N' ORDER BY U.ColumnName' + @CRLF +
N' FOR XML PATH(''''),TYPE).value(''.'',''nvarchar(MAX)''),1,2,N'''') AS Cols' + @CRLF +
N'FROM (SELECT DISTINCT [Group] FROM dbo.YourTable) YT' + @CRLF +
N'GROUP BY YT.[group];';
PRINT @SQL;
EXEC sp_executesql @SQL;
Loop through column names in SQL Server
You don't need Dynamic SQL for this. You can just approach the problem from a different (and thankfully also set-based) approach. Just cross-join your query to a numbers/tally table (or create one inline via CTE) for the number of columns (i.e. TOP(30) in your case) and then use a CASE statement to pick the column that you want. Each individual value from the numbers table/CTE will represent a row which equates to one of your hard-coded queries. Since each row is a different "query", each one can pull from a different A, B, or C column.
Test Setup:
SET NOCOUNT ON;
IF (OBJECT_ID(N'tempdb..#Bob') IS NOT NULL)
BEGIN
DROP TABLE #Bob;
END;
CREATE TABLE #Bob (
ChargeCode INT, EndShiftDate DATETIME, A1 INT, B1 INT, C1 INT,
A2 INT, B2 INT, C2 INT, A3 INT, B3 INT, C3 INT, A4 INT, B4 INT, C4 INT,
A5 INT, B5 INT, C5 INT, A6 INT, B6 INT, C6 INT, A7 INT, B7 INT, C7 INT,
A8 INT, B8 INT, C8 INT, A9 INT, B9 INT, C9 INT, A10 INT, B10 INT, C10 INT
);
INSERT INTO #Bob (ChargeCode, EndShiftDate, A1) VALUES (1, '2015-01-05', 1);
INSERT INTO #Bob (ChargeCode, EndShiftDate, A1) VALUES (1, '2015-01-05', 5);
INSERT INTO #Bob (ChargeCode, EndShiftDate, A1) VALUES (2, '2015-01-05', 3);
INSERT INTO #Bob (ChargeCode, EndShiftDate, A1) VALUES (2, '2015-01-05', 56);
INSERT INTO #Bob (ChargeCode, EndShiftDate, A1) VALUES (2, '2015-01-17', 300);
INSERT INTO #Bob (ChargeCode, EndShiftDate, A1) VALUES (2, '2015-01-17', 6);
INSERT INTO #Bob (ChargeCode, EndShiftDate, A1) VALUES (7, '2015-01-17', 10000);
INSERT INTO #Bob (ChargeCode, EndShiftDate, B1) VALUES (10, '2015-01-05', 11);
INSERT INTO #Bob (ChargeCode, EndShiftDate, B1) VALUES (10, '2015-01-05', 15);
INSERT INTO #Bob (ChargeCode, EndShiftDate, B1) VALUES (50, '2015-01-05', 13);
INSERT INTO #Bob (ChargeCode, EndShiftDate, B1) VALUES (50, '2015-01-05', 156);
INSERT INTO #Bob (ChargeCode, EndShiftDate, B1) VALUES (50, '2015-01-17', 1300);
INSERT INTO #Bob (ChargeCode, EndShiftDate, B1) VALUES (50, '2015-01-17', 16);
INSERT INTO #Bob (ChargeCode, EndShiftDate, B1) VALUES (77, '2015-01-17', 100000);
INSERT INTO #Bob (ChargeCode, EndShiftDate, C2) VALUES (200, '2015-02-05', 51);
INSERT INTO #Bob (ChargeCode, EndShiftDate, C2) VALUES (200, '2015-02-05', 55);
INSERT INTO #Bob (ChargeCode, EndShiftDate, C2) VALUES (100, '2015-02-05', 53);
INSERT INTO #Bob (ChargeCode, EndShiftDate, C2) VALUES (100, '2015-02-05', 556);
INSERT INTO #Bob (ChargeCode, EndShiftDate, C2) VALUES (100, '2015-02-17', 5300);
INSERT INTO #Bob (ChargeCode, EndShiftDate, C2) VALUES (100, '2015-02-17', 56);
INSERT INTO #Bob (ChargeCode, EndShiftDate, C2) VALUES (111, '2015-02-17', 1000000);
SELECT * FROM #Bob;
Single, non-Dynamic SQL query:
;WITH nums AS
(
SELECT TOP (6) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [TheNum]
FROM [sys].[objects]
), agg AS
(
SELECT b.ChargeCode,
DATEADD(DAY, (CEILING(nums.TheNum / 3.0) - 11), b.EndShiftDate) AS [EndShiftDate],
SUM(
CASE nums.TheNum
WHEN 1 THEN b.A1
WHEN 2 THEN b.B1
WHEN 3 THEN b.C1
WHEN 4 THEN b.A2
WHEN 5 THEN b.B2
WHEN 6 THEN b.C2
--repeat for 7, 8, 9 to be [ABC]3; 10, 11, 12 to be [ABC]4; and so on...
END
) AS [Hours]
FROM #Bob b
CROSS JOIN nums
WHERE 1 = CASE
WHEN nums.TheNum % 3 = 1 AND chargeCode IN (1, 2, 5) THEN 1 -- A
WHEN nums.TheNum % 3 = 2 AND chargeCode IN (10, 50, 70) THEN 1 -- B
WHEN nums.TheNum % 3 = 0 AND chargeCode IN (100, 200, 500) THEN 1 -- C
END
GROUP BY b.chargeCode, DATEADD(DAY, (CEILING(nums.TheNum / 3.0) - 11), b.EndShiftDate)
)
-- INSERT INTO myTable (ChargeCode, [EndShiftDate], [Hours])
SELECT ChargeCode, [EndShiftDate], [Hours]
FROM agg
WHERE agg.[Hours] IS NOT NULL;
Results (with the INSERT commented out):
ChargeCode EndShiftDate Hours
---------- ------------ -----
1 2014-12-26 00:00:00.000 6
2 2014-12-26 00:00:00.000 59
10 2014-12-26 00:00:00.000 26
50 2014-12-26 00:00:00.000 169
2 2015-01-07 00:00:00.000 306
50 2015-01-07 00:00:00.000 1316
100 2015-01-27 00:00:00.000 609
200 2015-01-27 00:00:00.000 106
100 2015-02-08 00:00:00.000 5356
Loop through SQL columns Dynamically
You can try this option:
column_names = ['column1', 'column2']
sql_cmd = (""" UPDATE table1 SET table1.{} = table2.{},
table1.{} = table2.{}
FROM table2 WHERE table1.id = table2.id""").format(column_names[0], column_names[0], column_names[1], column_names[1])
print(sql_cmd)
Sample Jinja2 Code using Macro:
{% macro macro_join_condition(tab_prefix_1, tab_prefix_2, columns) %}
{% for col in columns %}
{% if loop.first %}
{{ tab_prefix_1 }}{{ col }} = {{ tab_prefix_2 }}{{ col }}
{% else %}
and {{ tab_prefix_1 }}{{ col }} = {{ tab_prefix_2 }}{{ col }}
{% endif %}
{% endfor %}
{% endmacro %}
select *
from source_data sd right join target_data td on {{ macro_join_condition('td.','sd.', params.primaryKeyList) }}
Related Topics
What's the Purpose of SQL Keyword "As"
Postgresql: How to Convert from Unix Epoch to Date
SQL on Delete Cascade, Which Way Does the Deletion Occur
Row-Level Trigger VS Statement-Level Trigger
How to Find Out What Is Locking My Tables
How to Select a Record and Update It, with a Single Queryset in Django
How to Grant Myself Admin Access to a Local SQL Server Instance
Why Are Foreign Keys More Used in Theory Than in Practice
How to Interpret a Query's Explain Plan
How to Select Rows with Most Recent Timestamp for Each Key Value
Converting a String to Hex in SQL
Generate Default Values in a Cte Upsert Using Postgresql 9.3
SQL Server - Lack of Natural Join/X Join Y Using(Field)
How to Combine Aggregate Functions in MySQL
Extbase - Get Created SQL from Query
Difference Between Timestamps in Milliseconds in Oracle