Опубликовано: 27 ноя 06
Рейтинг:
Рейтинг:
Автор: Roman S. Golubin
Прислал: Roman S. Golubin
Вот запрос для SQL Server 2005, позволяющий сложить записи в одну строку:
CREATE TABLE dbo.TEST([Тип] INTEGER, [Имя] NVARCHAR(100), [Количество] INTEGER)
GO
INSERT dbo.TEST VALUES(1, N'Молоко', 5)
INSERT dbo.TEST VALUES(1, N'Сметана', 6)
INSERT dbo.TEST VALUES(2, N'Гвозди', 44)
INSERT dbo.TEST VALUES(3, N'Машины', 1)
GO
select [Тип],
[Описание] = replace((select [Имя] + ':' + cast([Количество] as varchar) as 'data()' from TEST where [Тип] = t.[Тип] for xml path('')), ' ', ',')
from dbo.TEST t
group by [Тип]
go
drop table dbo.TEST
Комментарии
.value('(./text())[1]' быстрее чем ).value('.',
SELECT [name], STUFF((
SELECT ', ' + c.[name]
FROM sys.columns c
WHERE c.[object_id] = t.[object_id]
FOR XML PATH(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 2, '')
FROM sys.objects t
WHERE t.[type] = 'U';
В несколько раз быстрее, лично проверено. На top 100000 отработало за 04:16 мин., в то время как одним из способов без xml path работало более 20 мин. и я не дождавшись, остановил выполнение запроса.
если в записях есть специальные символы типа <, >, &
DECLARE @Table TABLE(
ID INT,
Value VARCHAR(20)
)
INSERT INTO @Table SELECT 1,'One'
INSERT INTO @Table SELECT 2,'Two'
INSERT INTO @Table SELECT 3,'Three'
SELECT STUFF(
(
SELECT ', ' + CAST(ID AS VARCHAR(MAX)) + ': ' + Value
FROM @Table
FOR XML PATH(''), TYPE
).value('.','varchar(max)')
,1,2, ''
)
http://stackoverflow.com/questions/4944680/flatten-sql-server-table-to-a-string
В том смысле, что CHAR(160) выглядит в гриде MS Access 2003 как пробел, но не воспринимается как таковой функцией for xml path('')
//А если в тексте уже есть пробелы (например "Молоко свежее")?
Зависит, наверное, от клиента, но если вывод результата - в MS Access 2003, то можно все пробелы вначале заменить на CHAR(160)
CREATE TABLE dbo.TEST([Тип] INTEGER, [Имя] VARCHAR(100), [Количество] INTEGER)
GO
INSERT dbo.TEST VALUES(1, 'Мол'+CHAR(160)+'око', 5)
INSERT dbo.TEST VALUES(1, 'Сметана', 6)
INSERT dbo.TEST VALUES(2, 'Гвозди', 44)
INSERT dbo.TEST VALUES(3, 'Машины', 1)
GO
select [Тип], [Описание] =
replace((select [Имя] as 'data()' from TEST where [Тип] = t.[Тип] for xml path('')), ' ', CHAR(13) + CHAR(10))
from dbo.TEST t
group by [Тип]
go
drop table dbo.TEST
А если в тексте уже есть пробелы (например "Молоко свежее")?
_r2003
select '&'+'<'+'>'+'='+CHAR(0)+CHAR(1) for xml path('')
:))
select '&'+'<'+'>'+'='+CHAR(0)+CHAR(1) for xml path('')
Правда как оно по скорости в сравнении с вариантами: http://www.sql.ru/faq/faq_topic.aspx?fid=130 ?