多行以逗号分隔的值 [英] Multiple rows to comma separated values
问题描述
您好
我的数据位于 表格中,如下所示。在我看来,我需要将它们以逗号分隔。
样本表数据
DECLARE @ Table1 表 (
< span style ="font-size:10pt; font-family:Courier"> ID1 INT
, ID2 INT
, VALUE1 VARCHAR ( 20 )
, VALUE2 VARCHAR ( 20 ) )
插入 @ Table1
VALUES ( 1 , 20 , 'ABC' , 'DCB' )
,( 1 ; , 20 , 'XYZ' , 'CVB' )
,( 1 ; , 30 , 'BCD' , 'CBM' )
,( 2 ; , 50 , 'XYZ' , 'CVB' )
,( 2 ; , 50 , '' , 'CBS' )
OUT PUT:
| | ABC,XYZ | DCB,CVB |
| | BCD | CBM |
| | XYZ | CVB,CBS |
Suman Gupta
DECLARE @ Table1 TABLE(ID1 INT,ID2 INT,VALUE1 VARCHAR(20),VALUE2 VARCHAR(20))
INSERT INTO @ Table1 VALUES(1,20,'ABC','DCB')
,(1,20,'XYZ','CVB')
,( 1,30,'BCD','CBM')
,(2,50,'XYZ','CVB')
,(2,50,'','CBS')
SELECT t1.ID1,t1.ID2,
Stuff((SELECT','+ VALUE1
FROM @ Table1 t2
WHERE t2.ID1 = t1。 ID1和t2.ID2 = t1.ID2
FOR XML PATH(''),TYPE).value('。','varchar(max)'),1,1,'')AS VALUE1,
Stuff((SELECT','+ VALUE2
FROM @ Table1 t2
WHERE t2.ID1 = t1.ID1和t2.ID2 = t1.ID2
FOR XML PATH(''),TYPE).value('。','varchar (max)'),1,1,'')AS VALUE2
FROM @ Table1 t1
GROUP BY t1.ID1,t1.ID2;
Hi
I have data in a table as below. I need them to be comma separated in my view.
Sample Table Data
DECLARE@Table1 TABLE(
ID1 INT
,ID2 INT
,VALUE1 VARCHAR(20)
,VALUE2 VARCHAR(20) )
INSERT INTO@Table1
VALUES ( 1 ,20 ,'ABC','DCB')
,( 1 ,20 ,'XYZ','CVB')
,( 1 ,30 ,'BCD','CBM')
,( 2 ,50 ,'XYZ','CVB')
,( 2 ,50 ,'' ,'CBS')
OUT PUT :
1 |
20 |
ABC, XYZ |
DCB, CVB |
1 |
30 |
BCD |
CBM |
2 |
50 |
XYZ |
CVB, CBS |
Suman Gupta
DECLARE @Table1 TABLE(ID1 INT,ID2 INT,VALUE1 VARCHAR(20),VALUE2 VARCHAR(20) ) INSERT INTO @Table1 VALUES ( 1 ,20 ,'ABC','DCB') ,( 1 ,20 ,'XYZ','CVB') ,( 1 ,30 ,'BCD','CBM') ,( 2 ,50 ,'XYZ','CVB') ,( 2 ,50 ,'' ,'CBS') SELECT t1.ID1,t1.ID2, Stuff(( SELECT ',' + VALUE1 FROM @Table1 t2 WHERE t2.ID1 = t1.ID1 and t2.ID2 = t1.ID2 FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') AS VALUE1, Stuff(( SELECT ',' + VALUE2 FROM @Table1 t2 WHERE t2.ID1 = t1.ID1 and t2.ID2 = t1.ID2 FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') AS VALUE2 FROM @Table1 t1 GROUP BY t1.ID1,t1.ID2;
这篇关于多行以逗号分隔的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!