sql server - SQL How to Pivot Dynamic Rows from Lookup Table into Columns

Question

I have a lookup table that is maintained every other time and is populated with drivers to display in a report. The table looks like this:

Department	Driver
Store	Driver-1
Store	Driver-2
Store	Driver-3

Answer 1

Let's start off by addressing this statement:

I dont want to use dynamic sql, i need another solution that works

If you an indeterminate number of drivers, as I have mentioned in the comments, you need to use dynamic SQL. There's no way round that; you can't have non-dynamic SQL that results a dynamic number of columns that's not how the language works.

As you haven't mentioned what version of SQL Server you're using, I'm going to assume you're on a more recent version and thus have access to STRING_AGG. If not, you'll need to use the older FOR XML PATH method. As you don't define what the value in the row should be, I've guessed it should be the department's name

This gives the something like this:

CREATE TABLE dbo.YourTable (Department varchar(10),
                            Driver varchar(10));
GO

INSERT INTO dbo.YourTable
VALUES('Store','Driver-1'),
      ('Store','Driver-2'),
      ('Store','Driver-3');
GO

DECLARE @Dept varchar(10) = 'Store';

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
DECLARE @Delimiter nvarchar(30) = N',' + @CRLF + N'       ';

SELECT @SQL = N'SELECT ' + STRING_AGG(N'Department AS ' + QUOTENAME(Driver),@Delimiter) WITHIN GROUP (ORDER BY Driver ASC) + @CRLF +
              N'FROM dbo.YourTable YT' + @CRLF +
              N'WHERE Department = @Dept' + @CRLF +
              N'GROUP BY Department;'
FROM dbo.YourTable YT
WHERE Department = @Dept;

--PRINT @SQL; --Your best friend
EXEC sys.sp_executesql @SQL, N'@Dept varchar(10)', @Dept;
GO

DROP TABLE dbo.YourTable;

db<>fiddle