Как вывести значения столбца в строке через запятую на T-SQL? Microsoft SQL Server

Когда работаешь с базой данных, пишешь SQL запросы или инструкции, может возникнуть необходимость получить в виде текстовой строки значения определенного столбца таблицы, где каждое значение будет отделяться разделителем, например, запятой или точкой с запятой. В этой статье я покажу, как реализовать это на языке T-SQL, т.е. как получить значения столбца в строке через запятую.

В каких случаях может потребоваться получать значения столбца, разделенные запятой в строке? Например, самый простой вариант — это для реализации какой-нибудь динамической процедуры или инструкции (подробней об этой возможности языка T-SQL в материале – «Выполнение динамических T-SQL инструкций в Microsoft SQL Server»). Если приводить более конкретный пример, то на практике очень часто нужно реализовывать так называемый динамический PIVOT, для формирования аналитических отчетов. PIVOT, если кто не знает, — это оператор, с помощью которого можно формировать сводные таблицы, в частности осуществлять транспонирование таблицы (значения по горизонтали выводить по вертикали с агрегацией и группировкой).

Заметка! Начинающим рекомендую посмотреть мой видеокурс по T-SQL.

Итак, давайте приступим.

Исходные данные для примеров

Сначала давайте создадим данные, которые мы будем обрабатывать, чтобы суть наших действий была понятна.

Допустим, у нас будет таблица TestTable, которая будет содержать данные о товарах, такие как: идентификатор, наименование, цена и дата.

Примечание! Статья предполагает, что у Вас уже есть базовые знания языка T-SQL, поэтому о том, как создается таблица или как добавляются данные в таблицу, я пояснять не буду. Если Вы начинающий программист и у Вас нет базовых знаний языка SQL, то я Вам рекомендую почитать книгу «SQL код» – это самоучитель по данному языку. Книга написана мной, в ней я очень подробно рассказываю о языке SQL.

   
   --Создание таблицы
   CREATE TABLE TestTable(
        [ProductId]     [INT] IDENTITY(1,1) NOT NULL,
        [ProductName]   [VARCHAR](100) NOT NULL,
        [Price]                 [Money] NULL,
        [Dt]                    [DATETIME] NULL
   )
   GO
   --Добавление строк в таблицу
   INSERT INTO TestTable(ProductName, Price, Dt)
           VALUES ('Системный блок', 300, '01.12.2018'),
                      ('Монитор', 200, '01.01.2019'),
                          ('Клавиатура', 100, '01.02.2019'),
                          ('Мышь', 50, '01.03.2019'),
                          ('Принтер', 200, '01.04.2019')
   GO
   --Выборка данных
   SELECT * FROM TestTable

Заметка! Как сформировать и вставить в таблицу заданное количество строк на T-SQL.

Пример 1 – выводим все значения столбца в строке через запятую

Давайте представим, что нам нужно получить все значения из столбца «Наименование товара» (ProductName) в виде текстовой строки с разделителем.

В примере ниже мы напишем простую SQL инструкцию, в которой мы сохраним в переменной строку из значений столбца, разделенных запятыми.

   
   --Объявляем переменную для строки данных
   DECLARE @TextProduct NVARCHAR(MAX);
   --Формируем строку
   SELECT @TextProduct = ISNULL(@TextProduct + ', ','') + QUOTENAME(ProductName) 
   FROM TestTable;
   --Смотрим результат
   SELECT @TextProduct AS TextProduct

Суть данного метода проста, мы последовательно записываем в переменную значение за значением (то, что есть в переменной + текущее значение), по мере считывания данных из столбца, добавляя между значениями нужный нам разделитель.

Функцию ISNULL (проверка на NULL) мы использовали для того, чтобы определить, когда нам нужно вставлять первый разделитель, но в данном случае можно использовать и COALESCE, в чем разница можете почитать в статье «Функции COALESCE и ISNULL в T-SQL – особенности и основные отличия».

Функцию QUOTENAME мы используем для отделения каждого значения в строке, в нашем случае квадратными скобами. Это нужно для того, чтобы избежать ситуаций, когда в значении используются пробелы или другие символы. Если отделять значения не требуется, то эту функцию использовать необязательно, также можно вместо квадратных скобок указать другой символ в качестве выделения значения, например, кавычки, круглые или фигурные скобки и некоторые другие (необходимый символ указывается вторым параметром у функции QUOTENAME, по умолчанию квадратные скобки).

Пример 2 – выводим уникальные значения столбца в строке через запятую

В предыдущем примере мы выводили все данные из столбца, но в некоторых случаях все значения не нужны, например, для того же динамического PIVOT, где нужны только уникальные значения для формирования заголовков столбцов. Поэтому давайте рассмотрим пример, в котором мы предварительно обработаем наши исходные данные.

Допустим, нам нужно получить в текстовой строке года, которые есть в столбце «Дата» (Dt). При этом нам не нужны повторы, нам нужны только уникальные значения, для этого мы будем использовать ключевое слово DISTINCT.

В столбце Dt у нас хранится дата, поэтому для того, чтобы получить из даты год, мы используем функцию YEAR, подробней о том, как из даты получать различные ее части, мы разговаривали в материале – «Как из даты получить год, месяц или день в T-SQL?».

Для удобства я предлагаю использовать конструкцию WITH (обобщённое табличное выражение), так, на мой взгляд, наглядней.

   
   --Объявляем переменную для строки данных
   DECLARE @TextYear NVARCHAR(MAX);
   --Обобщённое табличное выражение
   WITH SRC AS (
        --Получаем уникальные значения
        SELECT DISTINCT YEAR(Dt) AS ProductYear
        FROM TestTable
   )
   --Формируем строку
   SELECT @TextYear = ISNULL(@TextYear + ', ','') + QUOTENAME(ProductYear) 
   FROM SRC;
   --Смотрим результат
   SELECT @TextYear AS TextYear

Как видим, у нас вывелось всего два значения.

Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения специально для начинающих.

У меня на этом все, надеюсь, материал был Вам интересен и полезен, пока!