Объединить значения строк T-SQL
Я пытаюсь собрать некоторые данные для отчета и нужно объединить значения строк одной из таблиц. Вот основная структура таблицы:
Отзывы
ReviewID
ReviewDate
Рецензенты
ReviewerID
ReviewID
UserID
Пользователи
UserID
FName
LName
Это отношение M: M. В каждом обзоре может быть много рецензентов; каждый Пользователь может быть связан со многими Обзорами.
В принципе, все, что я хочу увидеть, это Reviews.ReviewID, Reviews.ReviewDate и объединенная строка FName всех связанных пользователей для этого обзора (с разделителями-запятыми).
Вместо:
ReviewID---ReviewDate---User
1----------12/1/2009----Bob
1----------12/1/2009----Joe
1----------12/1/2009----Frank
2----------12/9/2009----Sue
2----------12/9/2009----Alice
Отобразите это:
ReviewID---ReviewDate----Users
1----------12/1/2009-----Bob, Joe, Frank
2----------12/9/2009-----Sue, Alice
Я нашел эту статью, описывающую некоторые способы сделать это, но большинство из них, похоже, касается только одной таблицы, а не несколько; к сожалению, мой SQL-fu недостаточно силен, чтобы адаптировать их к моим обстоятельствам. Меня особенно интересует пример на этом сайте, который использует FOR XML PATH(), поскольку он выглядит самым чистым и самым прямым.
SELECT p1.CategoryId,
( SELECT ProductName + ', '
FROM Northwind.dbo.Products p2
WHERE p2.CategoryId = p1.CategoryId
ORDER BY ProductName FOR XML PATH('')
) AS Products
FROM Northwind.dbo.Products p1
GROUP BY CategoryId;
Может ли кто-нибудь дать мне руку с этим? Любая помощь будет принята с благодарностью!
Ответы
Ответ 1
Посмотрите на это
DECLARE @Reviews TABLE(
ReviewID INT,
ReviewDate DATETIME
)
DECLARE @Reviewers TABLE(
ReviewerID INT,
ReviewID INT,
UserID INT
)
DECLARE @Users TABLE(
UserID INT,
FName VARCHAR(50),
LName VARCHAR(50)
)
INSERT INTO @Reviews SELECT 1, '12 Jan 2009'
INSERT INTO @Reviews SELECT 2, '25 Jan 2009'
INSERT INTO @Users SELECT 1, 'Bob', ''
INSERT INTO @Users SELECT 2, 'Joe', ''
INSERT INTO @Users SELECT 3, 'Frank', ''
INSERT INTO @Users SELECT 4, 'Sue', ''
INSERT INTO @Users SELECT 5, 'Alice', ''
INSERT INTO @Reviewers SELECT 1, 1, 1
INSERT INTO @Reviewers SELECT 2, 1, 2
INSERT INTO @Reviewers SELECT 3, 1, 3
INSERT INTO @Reviewers SELECT 4, 2, 4
INSERT INTO @Reviewers SELECT 5, 2, 5
SELECT *,
(
SELECT u.FName + ','
FROM @Users u INNER JOIN
@Reviewers rs ON u.UserID = rs.UserID
WHERE rs.ReviewID = r.ReviewID
FOR XML PATH('')
) AS Products
FROM @Reviews r
Ответ 2
Оказывается, есть еще более простой способ сделать это, что не требует UDF:
select replace(replace(replace((cast((
select distinct columnName as X
from tableName
for xml path('')) as varchar(max))),
'</X><X>', ', '),'<X>', ''),'</X>','')
Ответ 3
Имел подобную проблему и нашел сладкое решение после игры с кодом в течение 15 минут
declare @result varchar(1000)
select @result = COALESCE(@result+','+A.col1, A.col1)
FROM ( select col1
from [table]
) A
select @result
Возвращает результат как value1, value2, value3, value4
Наслаждайтесь;)
Ответ 4
SqlServer 2017 теперь имеет STRING_AGG, который объединяет несколько строк в один, используя данный разделитель.
Ответ 5
Есть три способа, которыми я имел дело с свертывающимися данными, как вы описали, 1. использовать курсор, 2. использовать UDF или 3. использовать пользовательскую совокупность (написанную в .NET CLR).
Курсор и UDF довольно медленные. (приблизительно 0,1 с в строке). Пользовательский агрегат CLR на удивление быстро. (приблизительно 0,001 сек за строку)
Microsoft отправляет код (чтобы сделать именно то, что вы хотите) как часть SDK для SQL 2005. Если вы его установили, вы сможете найти код в этой папке:
C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\StringUtilities.
Вы также можете захотеть эту статью в MSDN. В нем говорится об установке пользовательского агрегата и его включении:
http://msdn.microsoft.com/en-us/library/ms161551(SQL.90).aspx
После компиляции и установки настраиваемой совокупности вы должны иметь возможность запросить следующее:
SELECT Reviews.ReviewID, ReviewDate, dbo.StringUtilities.Concat(FName) AS [User]
FROM Reviews INNER JOIN Reviewers ON Reviews.ReviewID = Reviewers.ReviewID
INNER JOIN Users ON Reviews.UserID = Users.UserID
GROUP BY ReviewID, ReviewDate;
и получите набор результатов, как вы показали (выше)
Ответ 6
select p1.Availability ,COUNT(*),
(select name+',' from AdventureWorks2008.Production.Location p2 where
p1.Availability=p2.Availability for XML path(''),type).value('.','varchar(max)')
as Name from AdventureWorks2008.Production.Location p1 group by Availability
Результат
Availability COUNT Name
---------------------------------------------------------------------------------
0.00 7 Tool Crib,Sheet Metal Racks,Paint Shop,Paint Storage,Metal
Storage,Miscellaneous Storage,Finished Goods Storage,
80.00 1 Specialized Paint,
96.00 1 Frame Forming,
108.00 1 Frame Welding,
120.00 4 Debur and Polish,Paint,Subassembly,Final Assembly,
Ответ 7
A UDF будет хорошим способом решить эту проблему.
Просто определите функцию T-SQL (UDF), которая принимает int param (идентификатор продукта) и возвращает строку (объединение имен, связанных с продуктом.) Если ваше имя метода - GetProductNames, тогда ваш запрос может выглядеть следующим образом:
SELECT p1.CategoryId, dbo.GetProductNames(p1.CategoryId)
FROM Northwind.dbo.Products p1
GROUP BY CategoryId
Ответ 8
Попробуйте следующее:
Declare @Revs Table
(RevId int Priimary Key Not Null,
RevDt DateTime Null,
users varChar(1000) default '')
Insert @Revs (RevId, RevDt)
Select Distinct ReviewId, ReviewDate
From Reviews
Declare @UId Integer
Set @Uid = 0
While Exists (Select * From Users
Where UserID > @Uid)
Begin
Update @Revs Set
users = users + u.fName + ', '
From @Revs R
Join Reviewers uR On ur.ReviewId = R.RId
Join users u On u.UserId = uR.UserId
Where uR.UserId = @UId
Select @Uid = Min(UserId)
From users
Where UserId > @UId
End
Select * From @Revs
Ответ 9
Select R.ReviewID, ReviewDate
, (Select FName + ', '
from Users
where UserID = R.UserID
order by FName FOR XML PATH(')
) as [Users]
from Reviews
inner join Reviewers AS R
On Reviews.ReviewID = R.ReviewID
Group By R.ReviewID, ReviewDate;
Ответ 10
похоже, что вам нужна функциональность group_concat (из mysql). это было рассмотрено здесь для другого тестового набора данных: Как вернуть несколько значений в один столбец (T-SQL)?
Ответ 11
Создайте временную таблицу для выгрузки данных. Затем используйте метод FOR XML PATH. Внешний запрос необходим, чтобы обрезать последнюю запятую из списка.
CREATE TABLE #ReviewInfo (
ReviewId INT,
ReviewDate DATETIME,
Reviewer VARCHAR(1000))
INSERT INTO #ReviewInfo (ReviewId, ReviewDate, Reviewer)
SELECT r.ReviewId, r.ReviewDate, u.FName
FROM Reviews r
JOIN Reviewers rs ON r.ReviewId = rs.ReviewId
JOIN Users u ON u.UserId = rs.UserId
SELECT ReviewId, ReviewDate, LEFT(Users, LEN(Users)-1)
FROM (
SELECT ReviewId, ReviewDate,
(
SELECT Reviewer + ', '
FROM #ReviewInfo ri2
WHERE ri2.ReviewId = ri1.ReviewId
ORDER BY Reviewer
FOR XML PATH('')
) AS Users
FROM #ReviewInfo ri1
GROUP BY ReviewId, ReviewDate
) a
DROP TABLE #ReviewInfo
Ответ 12
select
p1.Availability,
COUNT(*),
(
select name+','
from AdventureWorks2008.Production.Location p2
where p1.Availability=p2.Availability
for XML path(''),type
).value('.','varchar(max)') as Name
from AdventureWorks2008.Production.Location p1
group by Availability
Ответ 13
Теперь с SQL Server 2017 появилась новая функция T-SQL STRING_AGG:
это новая агрегированная функция, которая объединяет значения строковых выражений и значения разделителей мест между ними.
Сепаратор не добавляется в конце строки.
Пример:
SELECT STRING_AGG ( ISNULL(FirstName,'N/A'), ',') AS csv
FROM Person.Person;
набор результатов:
John,N/A,Mike,Peter,N/A,N/A,Alice,Bob
Ответ 14
Когда количество элементов невелико, это может быть сделано с помощью ROW_NUMBER() OVER PARTITION BY:
declare @t table (col1 int, col2 varchar)
insert into @t VALUES (1,'A')
insert into @t VALUES (1,'B')
insert into @t VALUES (1,'C')
insert into @t VALUES (1,'D')
insert into @t VALUES (1,'E')
insert into @t VALUES (2,'X')
insert into @t VALUES (3,'Y')
select col1,
MAX(CASE seq WHEN 1 THEN col2 ELSE '' END ) +
MAX(CASE seq WHEN 2 THEN ', ' + col2 ELSE '' END ) +
MAX(CASE seq WHEN 3 THEN ', ' + col2 ELSE '' END ) +
MAX(CASE seq WHEN 4 THEN ', ' + col2 ELSE '' END ) +
MAX(CASE seq WHEN 5 THEN ',...' ELSE '' END )
as col2
from (
select col1, col2, ROW_NUMBER() OVER ( PARTITION BY col1 ORDER BY col2 ) seq
from @t
group by col1, col2
) x
group by col1
