Я использую универсальную систему для создания отчетов, которое берет данные из представления базы данных (SQL Server 2005). В этом представлении я должен был объединить данные из one-many отношений в одной строке и использовал решение, описанное priyanka.sarkar в этом потоке: Объедините несколько результатов в подзапросе в единственное разделенное от запятой значение. Решение использует SQLXML для слияния данных (подзапрос):
SELECT STUFF(
( SELECT ', ' + Name
FROM MyTable _in
WHERE _in.ID = _out.ID
FOR XML PATH('')), -- Output multiple rows as one xml type value,
-- without xml tags
1, 2, '') -- STUFF: Replace the comma at the beginning with empty string
FROM MyTable _out
GROUP BY ID -- Removes duplicates
Это работает отлично (это даже не настолько тяжело в производительности) кроме моих данных, теперь получает закодированный XML (и => &
и т.д.) SQLXML-I не хотел данных XML, в конце концов, я просто использовал это в качестве приема - и из-за универсальной системы я не могу кодировать вокруг этого для чистки его так, закодированные данные переходят прямо к отчету. Я не могу использовать хранимые процедуры с универсальной системой таким образом слияние КУРСОРА, или ОБЪЕДИНЯТЬ-ЛУГ не является опцией здесь...
Таким образом, то, что я ищу, является способом в T-SQL, который позволяет мне декодировать XML снова, или еще лучше: избегает SQLXML от кодирования его. Очевидно, я мог записать сохраненную функцию, которая делает это, но я предпочел бы встроенный, более безопасный способ...
Спасибо за помощь...
Если в качестве параметра для xml
указать type
можно использовать запрос XPath для преобразования типа XML обратно в varchar
. С примером переменной таблицы:
declare @MyTable table (id int, name varchar(50))
insert @MyTable (id, name) select 1, 'Joel & Jeff'
union all select 1, '<<BIN LADEN>>'
union all select 2, '&&BUSH&&'
Одно из возможных решений:
select b.txt.query('root').value('.', 'varchar(max)')
from (
select distinct id
from @MyTable
) a
cross apply
(
select CASE ROW_NUMBER() OVER(ORDER BY id) WHEN 1 THEN ''
ELSE ', ' END + name
from @MyTable
where id = a.id
order by
id
for xml path(''), root('root'), type
) b(txt)
Это будет печатать:
Joel & Jeff, <<BIN LADEN>>
&&BUSH&&
Вот альтернатива без преобразования XML. У него есть рекурсивный запрос, поэтому пробег производительности может варьироваться. Это из блога Квассного:
;WITH with_stats(id, name, rn, cnt) AS
(
SELECT id, name,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY name),
COUNT(*) OVER (PARTITION BY id)
FROM @MyTable
),
with_concat (id, name, gc, rn, cnt) AS
(
SELECT id, name,
CAST(name AS VARCHAR(MAX)), rn, cnt
FROM with_stats
WHERE rn = 1
UNION ALL
SELECT with_stats.id, with_stats.name,
CAST(with_concat.gc + ', ' + with_stats.name AS VARCHAR(MAX)),
with_stats.rn, with_stats.cnt
FROM with_concat
JOIN with_stats
ON with_stats.id = with_concat.id
AND with_stats.rn = with_concat.rn + 1
)
SELECT id, gc
FROM with_concat
WHERE rn = cnt
OPTION (MAXRECURSION 0)