Конкатенация строк
Часто в запросах приходится объединять несколько значений в одно текстовое значение (конкатенация). Например, может потребоваться получить строку следующего формата: ‘текущее время: 2019-02-19 15:28:44.049702+07’. Для выполнения конкатенации строк необходимо воспользоваться оператором || :
SELECT 'текущее время: ' || now() as time | time |
|---|
| текущее время: 2019-02-20 17:49:35.767265+07 |
Либо функцией concat :
SELECT concat('текущее время: ', now()) as time | time |
|---|
| текущее время: 2019-02-20 17:49:35.767265+07 |
Конкатенировать можно произвольное количество значений. Оператором ||
SELECT 'один, ' || 'два, ' || 'три' as result | result |
|---|
| один, два, три |
SELECT concat('один, ', 'два, ', 'три') as result | result |
|---|
| один, два, три |
+ (объединение строк) (Transact-SQL)
Оператор в строковом выражении, объединяющий две или более символьных или двоичных строки, два или более столбцов или несколько строк и имен столбцов в одно выражение (строковый оператор). Например, SELECT ‘book’+’case’; возвращает bookcase .
Синтаксис
expression + expression Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
выражение
Любое действительное выражение любого типа данных в категории символьных и двоичных данных, за исключением типов данных image, ntext и text. Оба выражения должны иметь одинаковый тип данных, или одно из выражений должно допускать неявное преобразование к типу данных другого выражения.
При сцеплении двоичных строк с любыми символами между двоичными строками необходимо использовать явное преобразование в символьные данные. В следующем примере показано, когда CONVERT или CAST должны использоваться с двоичной конкатенацией и когда CONVERT или CAST не нужно использовать.
DECLARE @mybin1 VARBINARY(5), @mybin2 VARBINARY(5) SET @mybin1 = 0xFF SET @mybin2 = 0xA5 -- No CONVERT or CAST function is required because this example -- concatenates two binary strings. SELECT @mybin1 + @mybin2 -- A CONVERT or CAST function is required because this example -- concatenates two binary strings plus a space. SELECT CONVERT(VARCHAR(5), @mybin1) + ' ' + CONVERT(VARCHAR(5), @mybin2) -- Here is the same conversion using CAST. SELECT CAST(@mybin1 AS VARCHAR(5)) + ' ' + CAST(@mybin2 AS VARCHAR(5)) Типы результата
Возвращает тип данных аргумента с самым высоким приоритетом. Дополнительные сведения см. в разделе Приоритет типов данных (Transact-SQL).
Замечания
При работе с пустыми строками нулевой длины оператор + (объединение строк) ведет себя иначе, чем при работе со значениями NULL или с неизвестными значениями. Символьная строка символа нулевой длины может быть указана в виде двух одинарных кавычек без каких-либо символов между ними. Двоичная строка нулевой длины может быть указана как 0x без указания каких-либо байтовых значений в шестнадцатеричной константе. При сцеплении строки нулевой длины всегда сцепляются две указанные строки. При работе со строками со значением NULL результат объединения зависит от настроек сеанса. При присоединении нулевого значения к известному значению результатом будет неизвестное значение, объединение строк с нулевым значением также дает нулевое значение, как и в арифметических действиях с нулевыми значениями. Однако можно изменить данное поведение, поменяв значение CONCAT_NULL_YIELDS_NULL для текущего сеанса. Дополнительные сведения см. в статье SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).
Если результат объединения строк превышает предел в 8 000 байт, то он усекается. Однако усечения не произойдет, если хотя бы одна из сцепляемых строк принадлежит к типу больших значений.
Примеры
А. Использование объединения строк
В следующем примере создается единственный столбец с заголовком Name из нескольких символьных столбцов, где за фамилией лица следуют запятая, один пробел и имя того же лица. Результирующий набор сортируется в алфавитном порядке по возрастанию, сначала по фамилии, а затем по имени.
-- Uses AdventureWorks SELECT (LastName + ', ' + FirstName) AS Name FROM Person.Person ORDER BY LastName ASC, FirstName ASC; B. Объединение числовых типов данных и дат
В приведенном ниже примере функция CONVERT используется для объединения типов данных numeric и date.
-- Uses AdventureWorks SELECT 'The order is due on ' + CONVERT(VARCHAR(12), DueDate, 101) FROM Sales.SalesOrderHeader WHERE SalesOrderID = 50001; GO ------------------------------------------------ The order is due on 04/23/2007 (1 row(s) affected) C. Использование объединения нескольких строк
В следующем примере объединяются несколько строк для формирования одной длинной строки для отображения фамилии и первого инициала вице-президентов в Adventure Works Cycles. После фамилии ставится запятая, а после первой буквы инициалов — точка.
-- Uses AdventureWorks SELECT (LastName + ',' + SPACE(1) + SUBSTRING(FirstName, 1, 1) + '.') AS Name, e.JobTitle FROM Person.Person AS p JOIN HumanResources.Employee AS e ON p.BusinessEntityID = e.BusinessEntityID WHERE e.JobTitle LIKE 'Vice%' ORDER BY LastName ASC; GO Name Title ------------- ---------------` Duffy, T. Vice President of Engineering Hamilton, J. Vice President of Production Welcker, B. Vice President of Sales (3 row(s) affected) D. Использование больших строк при объединении
В приведенном ниже примере выполняется объединение нескольких строк в одну длинную строку, а затем предпринимается попытка вычислить длину итоговой строки. Итоговая длина результирующего набора равна 16 000, так как вычисление выражения начинается слева: @x + @z + @y => (@x + @z) + @y. В этом случае результат (@x + @z) усекается до 8000 байтов, а затем в результирующий набор добавляется значение @y, после чего длина итоговой строки становится равна 16 000. Так как @y — это строка типа с большим значением, усечения не происходит.
DECLARE @x VARCHAR(8000) = REPLICATE('x', 8000) DECLARE @y VARCHAR(max) = REPLICATE('y', 8000) DECLARE @z VARCHAR(8000) = REPLICATE('z',8000) SET @y = @x + @z + @y -- The result of following select is 16000 SELECT LEN(@y) AS y GO y ------- 16000 (1 row(s) affected) Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)
Д. Использование объединения нескольких строк
В приведенном ниже примере несколько строк сцепляются в одну длинную строку для отображения фамилий и инициалов имен вице-президентов из образца базы данных. После фамилии ставится запятая, а после первой буквы инициалов — точка.
-- Uses AdventureWorks SELECT (LastName + ', ' + SUBSTRING(FirstName, 1, 1) + '.') AS Name, Title FROM DimEmployee WHERE Title LIKE '%Vice Pres%' ORDER BY LastName ASC; Name Title ------------- --------------- Duffy, T. Vice President of Engineering Hamilton, J. Vice President of Production Welcker, B. Vice President of Sales Как объединить строки в SQL?
Можно повелосипедить ))). Могу лишь предложить напрвление для поиска решения.
Зная сколько максимально может быть новых столбцов можно сначала сконкатенировать все в один (STRING_AGG).
А затем вот так — https://www.mssqltips.com/sqlservertip/6321/split-.
при этом для каких-то позиций часть столбцов будет пустая, а для каких-то они все будут заполнены.
DD-var, один столбец или несколько? сколько? записей с одинаковым detal можно быть бесконечно много. (ну так то).
И получается, что в вашем примере не хватает столбца mono4 со значением — ступицы.
Решения вопроса 1
alexalexes @alexalexes
Лучше, конечно, вертикальную выборку перерабатывать в горизонтальную не в SQL, а в той процедурной прослойке, которая вызывает запрос.
На SQL можно такое провернуть, но будет не универсально (фиксированное число столбцов в итоговой выборке).
with main_tb (id, detal, mono, row_num) as (select id, detal, mono row_number() over (partition by detal order by id) as row_num from tb) select t.id, t.detal, t.name, (select t1.mono from main_tb as t1 where t1.detal = t.detal and t1.row_num = 1) mono, (select t1.mono from main_tb as t1 where t1.detal = t.detal and t1.row_num = 2) mono2, (select t1.mono from main_tb as t1 where t1.detal = t.detal and t1.row_num = 3) mono3 from tb as tОтвет написан более года назад
Комментировать
Нравится Комментировать
Ответы на вопрос 0
Ваш ответ на вопрос
Войдите, чтобы написать ответ
- SQL Server
Чем MSSQL занимает всю оперативную память?
- 2 подписчика
- 16 янв.
- 201 просмотр
- SQL Server
- +1 ещё
Как добавить создание таблицы в базу данных sql server?
- 1 подписчик
- 16 янв.
- 46 просмотров
Конкатенация строк в T-SQL. Способы используемые в Microsoft SQL Server
Всем привет! Сегодня мы рассмотрим несколько способов конкатенации строк, которые можно использовать в Microsoft SQL Server на языке T-SQL.
Что такое конкатенация строк
Конкатенация – это операция соединения нескольких текстовых строк в одну.
Например, если выполнить конкатенацию двух отдельных строк «Язык» и «SQL», то получится одна строка «ЯзыкSQL».
В SQL конкатенация используется для соединения текстовых значений нескольких столбцов, или соединения значения столбца с константной строкой.
Конкатенация строк в Microsoft SQL Server
В Microsoft SQL Server на языке T-SQL мы можем использовать несколько способов для конкатенации строк, в частности два: оператор + и функцию CONCAT.
Давайте подробнее рассмотрим каждый из этих способов. Однако сначала давайте создадим тестовые данные, чтобы наглядно видеть результат работы конкатенации строк.
Исходные данные для примеров
Допустим, что у нас есть таблица, которая содержит Фамилию Имя Отчество клиентов. При этом каждую часть ФИО мы храним в отдельном столбце.
CREATE TABLE Customers ( Customer_Id INT NOT NULL IDENTITY(1,1), SurName VARCHAR(100) NULL, FirstName VARCHAR(100) NULL, Patronymic VARCHAR(100) NULL ); GO INSERT INTO Customers (SurName, FirstName, Patronymic) VALUES ('Иванов', 'Сергей', 'Иванович'), ('Попов', 'Роман', 'Алексеевич'), ('Сергеев', 'Петр', 'Вячеславович'), ('John', 'Smith', NULL), ('Oliver', 'Adamson', NULL); GO SELECT * FROM Customers;
Заметка! Всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней очень подробно рассмотрены основные конструкции языка.
Использование оператора +
Первый и часто используемый способ конкатенации строк в Microsoft SQL Server, который реализован уже очень давно и является, наверное, классическим – это способ с использованием оператора +.
Принцип его работы следующий: мы указываем текстовое значение, им может быть как значение из столбца, так и константное текстовое значение, после него пишем +, а затем другое текстовое значение, которое мы хотим присоединить к первому текстовому значению. И таким образом мы можем объединить несколько строк в одну строку.
Синтаксис примерно следующий
Строка1 + Строка2 + Строка3 +…… Строка n = Итоговая строка
А теперь давайте представим, что нам необходимо вывести Фамилию Имя Отчество из нашей таблицы в одной строке.
Для этого мы можем использовать оператор + и написать следующий запрос.
SELECT SurName + ' ' + FirstName + ' ' + Patronymic AS [ФИО] FROM Customers;
В данном случае после каждого столбца мы вставляем еще и пробел, для того чтобы ФИО не сливалось в одну непрерывную строку.
Как было отмечено ранее, вместо названия столбца мы можем указывать любую текстовую строку, например, следующим образом.
SELECT 'Фамилия' + 'Имя' + 'Отчество' AS [ФИО]
Вы наверняка заметили, что в первом примере у некоторых строк результирующего набора данных у нас выводится значение NULL. Это происходит потому, что в некоторых записях нашей таблицы нет значений, т.е. в них хранится NULL, в частности у записей с идентификаторами 4 и 5.
А оператор + в случае соединения нормального значения со значением NULL будет возвращать NULL. Иными словами, если в любой из строк, которые мы соединяем с помощью оператора +, будет NULL, то результат всей конкатенации также будет NULL. И это является одним из главных недостатков данного способа конкатенации строк в T-SQL.
Конечно же, мы можем легко устранить данную проблему, обернув столбец, например, функцией ISNULL, которая возвращает значение второго параметра, если значение первого NULL.
Таким образом, чтобы предотвратить появление NULL значений в нашем запросе, мы можем с помощью функции ISNULL проверять значения столбцов и, если там NULL, выводить просто пусто.
SELECT ISNULL(SurName,'') + ' ' + ISNULL(FirstName,'') + ' ' + ISNULL(Patronymic,'') AS [ФИО] FROM Customers;
Как видим, в данном случае результат уже более корректен.
Использование функции CONCAT
Вторым способом конкатенации строк в Microsoft SQL Server является способ с использованием функции CONCAT.
CONCAT – функция SQL, которая соединяет несколько текстовых значений в одну строку, другими словами, выполняет конкатенацию строк.
В данной функции уже реализована проверка на NULL, и нам не требуется самостоятельно проверять столбец на наличие таких значений и пропускать все значения столбца через функцию ISNULL.
Однако данная функция появилось лишь в 2012 версии SQL Server, т.е. до 2012 версии можно использовать только оператор +.
Принцип работы функции CONCAT следующий: она объединяет все параметры, которые мы передаем в эту функцию, в одну строку. Параметров можно передавать несколько, а в качестве них можно указывать как константную строку, т.е. просто текст, так и столбец таблицы.
Давайте напишем SQL запрос, который будет эквивалентен нашему предыдущему запросу.
SELECT CONCAT(SurName, ' ', FirstName, ' ', Patronymic) AS [ФИО] FROM Customers;
Как видим, результат точно такой же.
На сегодня это все, надеюсь, материал был Вам полезен, пока!