从查询中获取逗号分隔的结果 [英] get comma seperated results from query

问题描述

大家好，

我有一个要查询的查询，我觉得很难查询.

我有一个具有以下结构的表:

ID字段
1''aaaa''
1''bbbb''
2''cccc''
2''dddd''

我想要查询的输出如下:

1''aaaa''，''bbbb''
2''cccc''，''dddd''

 创建  TABLE  #a
(
id  INT ，
字段 varchar ( 1000 )
)

插入  INTO  #a
        (id，field)
值( 1 ，-  id-int 
          '  aaaaa' - 字段-varchar(1000)
          )
          
插入  INTO  #a
        (id，field)
值( 1 ，-  id-int 
          '  bbbb' - 字段-varchar(1000)
          )
          
插入  INTO  #a
        (id，field)
值( 2 ，-  id-int 
          '  cccc' - 字段-varchar(1000)
          )
          
插入  INTO  #a
        (id，field)
值( 2 ，-  id-int 
          '  ffff' - 字段-varchar(1000)
          )

请帮助我...

问候，
Gopal

解决方案

请检查以下链接以了解其他选项:

http://stackoverflow.com/Questions/1817985/how-do-i-create-a-comma-separated-list-using-a-sql-query [ SELECT id，( 选择字段+ ' ，' FROM tableName WITH ( NOLOCK ) 位置 a.id = id 订单 BY 场地 FOR XML PATH(' ') ) AS 字段 FROM 表命名一个 WITH ( NOLOCK ) 组 BY id

Hi All,

I have a query to make which i m finding difficult to make.

I have a table with following structure:

Id Field
1 ''aaaa''
1 ''bbbb''
2 ''cccc''
2 ''dddd''

I want the output from the query as follows:

1 ''aaaa'',''bbbb''
2 ''cccc'',''dddd''

CREATE TABLE #a
(
id INT ,
field varchar(1000)
)

INSERT INTO #a
        ( id, field )
VALUES  ( 1, -- id - int
          'aaaaa'  -- field - varchar(1000)
          )
          
INSERT INTO #a
        ( id, field )
VALUES  ( 1, -- id - int
          'bbbb'  -- field - varchar(1000)
          )
          
INSERT INTO #a
        ( id, field )
VALUES  ( 2, -- id - int
          'cccc'  -- field - varchar(1000)
          )
          
INSERT INTO #a
        ( id, field )
VALUES  ( 2, -- id - int
          'ffff'  -- field - varchar(1000)
          )

Please help me .....

Regards,
Gopal

解决方案

Please check the following link for different options:

http://stackoverflow.com/questions/1817985/how-do-i-create-a-comma-separated-list-using-a-sql-query[^]

I''m not a sql expert, but what you want to investigate the following t-sql commands:

COALESCE<br />
<br />
PIVOT

The PIVOT command will arrange the data the way you want it, and the COALESCE command will help you put the resulting dataset into a comma-delimited string.

Google is your friend.

Hi, gopalgupta

try this one.

SELECT id, (
           SELECT field+', '
           FROM  tableName WITH (NOLOCK)
           WHERE  a.id = id
           ORDER BY
                  field
                  FOR XML PATH('')
       ) AS field
FROM   tableName a WITH (NOLOCK)
GROUP BY
       id

这篇关于从查询中获取逗号分隔的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！