TSQL- csv for a column and not for the rest

问题:

i need to get csv from a column but the minimum value from the rest of the columns (or any value because they are same for a group). As an example;

I have the following table:

COL1        COL2        COL3        COL4
------------------------------------------
ABC         10          35          GROUP1
AQW         10          35          GROUP1
VBN         10          35          GROUP1
HJK         10          35          GROUP1
DFV         30          25          GROUP2
HYT         30          25          GROUP2
DET         30          25          GROUP2


And I want the following result:

COL1                    COL2        COL3        COL4
--------------------------------------------------------
ABC,AQW,VBN,HJK         10          35          GROUP1
DFV,HYT,DET             30          25          GROUP2

I looked at similar scenarios and the solution of using a variable-case-concatenation (Concatenating Column Values into a Comma-Separated List) would not work as I will be having multiple groups. I can't think of a way doing that. Can you suggest a way?

回答1:

Sample Data

DECLARE @Table1  TABLE 
    (COL1 varchar(3), COL2 int, COL3 int, COL4 varchar(6))
;

INSERT INTO @Table1
    (COL1, COL2, COL3, COL4)
VALUES
    ('ABC', 10, 35, 'GROUP1'),
    ('AQW', 10, 35, 'GROUP1'),
    ('VBN', 10, 35, 'GROUP1'),
    ('HJK', 10, 35, 'GROUP1'),
    ('DFV', 30, 25, 'GROUP2'),
    ('HYT', 30, 25, 'GROUP2'),
    ('DET', 30, 25, 'GROUP2')
;

Script :

SELECT   COL1 = STUFF(
             (SELECT ',' + COL1 
              FROM @Table1 t1
              WHERE t1.COL2 = t2.COL2
              FOR XML PATH (''))
             , 1, 1, ''),COL2, COL3, COL4 from @Table1 t2
group by COL2, COL3, COL4;

回答2:

Use STUFF with GROUP BY.

Query

SELECT STUFF((SELECT ',' + [COL1] 
FROM [your_table_name]
WHERE ([COL2] = t.[COL2] AND [COL3] = t.[COL3] AND [COL4] = t.[COL4]) 
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
   ,1,1,'') AS COL1, [COL2], [COL3], [COL4]
FROM [your_table_name] t
GROUP BY [COL2], [COL3], [COL4]

回答3:

You can do it with a sub query, like this:

Create and populate sample table (Please save us this step in your future questions)

DECLARE @T AS TABLE
(
    COL1 char(3),
    COL2 int,
    COL3 int,
    COL4 char(6)
)

INSERT INTO @T VALUES
('ABC', 10, 35, 'GROUP1'),
('AQW', 10, 35, 'GROUP1'),
('VBN', 10, 35, 'GROUP1'),
('HJK', 10, 35, 'GROUP1'),
('DFV', 30, 25, 'GROUP2'),
('HYT', 30, 25, 'GROUP2'),
('DET', 30, 25, 'GROUP2')

The query:

SELECT DISTINCT
       STUFF(
       (
           SELECT ',' + COL1
           FROM @T
           WHERE COL2 = t.COL2
           AND COL3 = t.COL3
           AND COL4 = t.COL4
           FOR XML PATH('')
       )
       , 1, 1, '') As COL1,
       COL2,
       COL3,
       COL4
 FROM @T t

Results:

COL1                COL2    COL3    COL4
ABC,AQW,VBN,HJK     10      35      GROUP1
DFV,HYT,DET         30      25      GROUP2