Microsoft SQL Server
Transact-SQL

Сложение символьных полей в запросе для SQL Server 2005

Опубликовано: 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 ?



Необходимо войти на сайт, чтобы оставлять комментарии

Раздел FAQ: Microsoft SQL Server / Transact-SQL / Сложение символьных полей в запросе для SQL Server 2005