时间戳

首页 > 解决方案 > populate two variables in one query

sql-server - populate two variables in one query

问题描述

I am writing a stored procedure with a pivot in it. The pivot field names can change depending on the data in the table.

So I have the two variables below. However this seems quite inefficient because I run two queries on the same table, this is probably due to my lack of knowledge.

declare @code nvarchar(max) = ''
select @code = @code + '[' + Code + '],' from (select Code from myTbl) as c 
set @code = substring(@code , 1, len(@code ) - 1)

declare @Name nvarchar(max) = ''
select @Name = @Name + '[' + Name + '],' from (select Name from myTbl) as c
set @Name = substring(@Name , 1, len(@Name ) - 1)

Is it possible to populate both variables and only query the table once?

标签： sql-serversql-server-2012

解决方案

是的，您可以，这是一个简单的示例

CREATE TABLE T(
  Code VARCHAR(45),
  Name VARCHAR(45)
);

INSERT INTO T VALUES
('Code1', 'Name1'),
('Code2', 'Name2');

DECLARE @Code VARCHAR(MAX) = '',
        @Name VARCHAR(MAX) = '';

SELECT @Code = @Code + QUOTENAME(Code) + ',',
       @Name = @Name + QUOTENAME(Name) + ','
FROM T;

SELECT @Code, @Name;

回报：

+------------------+------------------+
| No column name)  | (No column name) |
+------------------+------------------+
| [Code1],[Code2], | [Name1],[Name2], |
+------------------+------------------+

如果您有 SQL Server 2017，则无需使用子字符串，您可以使用STRING_AGG()as

SELECT STRING_AGG(QUOTENAME(Code), ','),
       STRING_AGG(QUOTENAME(Name), ',')
FROM T;

回报：

+------------------+------------------+
| (No column name) | (No column name) |
+------------------+------------------+
| [Code1],[Code2]  | [Name1],[Name2]  |
+------------------+------------------+

推荐阅读