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
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…