Loop through temp table and use column values as column names in a select statement in another query

Question

I am creating a temp table and want to loop through the rows in that temp table and then use the values as column names in a select statement in another query.

Temp Table

Reviewer    |  Status
---------------------------
Reviewer1   |  Under Review
Reviewer2   |  Approved
Reviewer3   |  Denied
Reviewer4   |  Under Review

Desired Result

Reviewer1     |  Reviewer2     |  Reviewer3     |  Reviewer4
------------------------------------------------------------
Under Review  |  Approved      |  Denied        |  Under Review

I've followed this example: Setting column values as column names in the SQL query result and can get it to work with a pre-defined numbers of rows/values in the temp table, but I will have an undefined number of Reviewers and I can't get the dynamic select version to work because SQL does not have a Group_Contact function.

I've been stuck on this awhile and would appreciate any advice.

One way to do it would be with the use of `PIVOT` through dynamic SQL. — BJones, Dec 18 '19 at 18:01
As BJones commented, `PIVOT`... my first instinct when requirement involves transpose ("columns becomes rows" or vice versa) — Eric Lease, Dec 18 '19 at 19:08

score 0 · Accepted Answer · answered Dec 18 '19 at 18:17

Perhaps this script will help. As I mentioned above it's using PIVOT with dynamic SQL.

CREATE TABLE Review (Reviewer VARCHAR(100), Status VARCHAR(100))
INSERT INTO Review
VALUES ('Reviewer1','Under Review'),
       ('Reviewer2','Approved'),
       ('Reviewer3','Denied'),
       ('Reviewer4','Under Review'),
       ('Reviewer5','Denied'),
       ('Reviewer6','Under Review');

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(Name)
  FROM (SELECT p.Reviewer AS Name
        FROM Review AS p
        GROUP BY p.Reviewer) AS x;
SET @sql = N'
SELECT ' + STUFF(@columns, 1, 2, '') + '
FROM
(
  SELECT p.Reviewer AS Name, p.Status
   FROM Review AS p
) AS j
PIVOT
(
  MAX(Status) FOR Name IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;

This is exactly what I was looking for. Thanks for the help and quick solution. — lorigar, Dec 18 '19 at 18:47

Loop through temp table and use column values as column names in a select statement in another query

1 Answers1