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

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

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

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

Скриншот 1

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

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

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

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

Скриншот 2

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

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

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

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

Скриншот 3

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

Функцию 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

Скриншот 4

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

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

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Комментарии: 3
  1. Аватар
    Александр

    Раз зашел разговор про конкатенацию строки, пользуясь случаем, хочу спросить, как преодолеть ограничение длины строки в 4000 символов?
    В частности хочу собрать json структуру и столкнулся с этой проблемой

    1. Админ
      Админ (автор)

      Для этого можно использовать тип данных NVARCHAR(MAX), у него таких ограничений нет.

  2. Аватар
    Евгений

    Я всегда делаю вот таким образом:
    SELECT field1+’,’ FROM tableTest er FOR XML PATH(»)
    с уникальным полем — так: SELECT DISTINCT field1+’,’ FROM tableTest er FOR XML PATH(»)
    Один недостаток — работает начиная с версий 2012.. если не ошибаюсь.
    Как то больше нравиться, да и конструкции потом проще воспринимаются. :smile:
    Или я что то не понял?

Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.