Best Way to Iterate Through Columns in a SQL Table

Is there any way to iterate through each column in a table in SQL Server

Not done with a while loop but a quick and easy way to find any column where the name like.

SELECT   
        c.name  AS 'ColumnName'
        ,t.name AS 'TableName'
FROM        
        sys.columns c
JOIN        
        sys.tables  t   ON c.object_id = t.object_id
WHERE      
        t.name = 'TableName'
        AND c.name LIKE 'box%'
ORDER BY    
         TableName
        ,ColumnName;

Answer updated to now include the table name in the where clause.

best way to iterate through columns in a SQL table?

This may work for you if there are no NULLs in your data already:

create table t4 (
    id int identity(1,1) not null primary key clustered,
    v1 varchar(10) null,
    v2 varchar(10) null,
    v3 varchar(10) null,
    v4 varchar(10) null
)

insert t4 (v1, v2, v3, v4)
values  ('a', 'b', 'c', 'd'),
        ('a', 'b', 'c', ''),
        ('a', 'b', '', ''),
        ('a', '', '', ''),
        ('', '', '', '')

update  t4
set     v4 = case when v4 = '' then null else v4 end,
        v3 = case when v3 + v4 = '' then null else v3 end,
        v2 = case when v2 + v3 + v4 = '' then null else v2 end,
        v1 = case when v1 + v2 + v3 + v4 = '' then null else v1 end

Before:

+----+----+----+----+----+
| id | v1 | v2 | v3 | v4 |
+----+----+----+----+----+
|  1 | a  | b  | c  | d  |
|  2 | a  | b  | c  |    |
|  3 | a  | b  |    |    |
|  4 | a  |    |    |    |
|  5 |    |    |    |    |
+----+----+----+----+----+

After:

+----+------+------+------+------+
| id |  v1  |  v2  |  v3  |  v4  |
+----+------+------+------+------+
|  1 | a    | b    | c    | d    |
|  2 | a    | b    | c    | NULL |
|  3 | a    | b    | NULL | NULL |
|  4 | a    | NULL | NULL | NULL |
|  5 | NULL | NULL | NULL | NULL |
+----+------+------+------+------+

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;

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 to iterate through table using columns as variables

Here is an imagination for what you may need:

select max(LastPurchase ) as LastPurchase ,Item_Code,CustomerNumber
group by Item_Code,CustomerNumber

Loop through columns SQL

Well, in case of a known number of columns, you can do:

SELECT  
  MyName + " ->"
  + case OPTION1 when 1 then ' OPTION1' else '' end
  + case OPTION2 when 1 then ' OPTION2' else '' end
  + ...
FROM
 Table

If columns are unknown when you create the query - I'd probably still go that way with some dynamically created SQL. The advantage is that the code probably does what you wants and is very simple.

loop through each column of a table

Test Data

declare @ExcelData TABLE (Id INT, PId INT, PType VARCHAR(20)
, CId VARCHAR(20), CNotes VARCHAR(20), CLegal VARCHAR(20))
INSERT INTO @ExcelData VALUES
(1  ,  101  ,'test',  '201'  ,'notes','testlegal'),
(2  ,  102  ,'test',  '202'  ,'notes','testlegal'),
(3  ,  103  ,'test',  '203'  ,'notes','testLegal')

Query

SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) New_ID
       ,*
FROM @ExcelData t
 UNPIVOT (Value FOR Attributes IN (CId, CLegal,CNotes))up

Result

╔════════╦════╦═════╦═══════╦═══════════╦════════════╗
║ New_ID ║ Id ║ PId ║ PType ║   Value   ║ Attributes ║
╠════════╬════╬═════╬═══════╬═══════════╬════════════╣
║      1 ║  1 ║ 101 ║ test  ║ 201       ║ CId        ║
║      2 ║  1 ║ 101 ║ test  ║ testlegal ║ CLegal     ║
║      3 ║  1 ║ 101 ║ test  ║ notes     ║ CNotes     ║
║      4 ║  2 ║ 102 ║ test  ║ 202       ║ CId        ║
║      5 ║  2 ║ 102 ║ test  ║ testlegal ║ CLegal     ║
║      6 ║  2 ║ 102 ║ test  ║ notes     ║ CNotes     ║
║      7 ║  3 ║ 103 ║ test  ║ 203       ║ CId        ║
║      8 ║  3 ║ 103 ║ test  ║ testLegal ║ CLegal     ║
║      9 ║  3 ║ 103 ║ test  ║ notes     ║ CNotes     ║
╚════════╩════╩═════╩═══════╩═══════════╩════════════╝

Note

Columns used in UNPIVOT MUST BE of the same data type if they are not use a sub-query to cast/convert them to a same datatype and the rest should be the same. In this example columns CId, CLegal,CNotes must be of the same datatype.

SQL. Looping through columns from different tables to calculate value

First, this is not a good table structure. If you want to store this kind of data, it's best to create another key and store the amount_drivers columns as a single column over multiple rows. The same goes for SPEED_CAT. As it is, there isn't much reason to use the SPEED_CATEGORIES table since you have a fixed number of columns. However, just in case these values change, I've used them below.

Second, this is the best I can glean from your structure and desired output without data to support it. See the comments on your question.

select
    Trf.ID,
    (
        case when Spd.MAX_SPEED <= Cat.SPEED_CAT_1 then Trf.amount_drivers1 else 0 end
        + case when Spd.MAX_SPEED <= Cat.SPEED_CAT_2 then Trf.amount_drivers2 else 0 end
        + case when Spd.MAX_SPEED <= Cat.SPEED_CAT_3 then Trf.amount_drivers3 else 0 end
        + case when Spd.MAX_SPEED <= Cat.SPEED_CAT_4 then Trf.amount_drivers4 else 0 end
        + case when Spd.MAX_SPEED <= Cat.SPEED_CAT_5 then Trf.amount_drivers5 else 0 end
        + case when Spd.MAX_SPEED <= Cat.SPEED_CAT_6 then Trf.amount_drivers6 else 0 end
        + case when Spd.MAX_SPEED <= Cat.SPEED_CAT_7 then Trf.amount_drivers7 else 0 end
        + case when Spd.MAX_SPEED <= Cat.SPEED_CAT_8 then Trf.amount_drivers8 else 0 end
        + case when Spd.MAX_SPEED <= Cat.SPEED_CAT_9 then Trf.amount_drivers9 else 0 end
        + case when Spd.MAX_SPEED <= Cat.SPEED_CAT_10 then Trf.amount_drivers10 else 0 end
    ) TO_HARD
from
    TRAFFIC Trf
    join MAX_SPEED Spd Trf.MAX_SPEED_KEY = Spd.ID
    join SPEED_CATEGORIES Cat on Trf.SPEED_CATEGORIE_KEY = Cat.ID

---

Related Topics