Stuffing data of one column when data in all other column same

Question

I am trying to concatenate value present in params and Note separated by semicolon whenever data in other column same.

when data in a column AppID ,FileID Action ,ID ,BaseVehicleID ,ParttypeID ,SubModelID are same then only concatenate the params and note else not, and display it as individual record in output.

Sample data:

DECLARE @TABLE TABLE
(
AppID INT,  
FileID INT,
Action CHAR (1),    
ID INT, 
BaseVehicleID INT,  
ParttypeID INT, 
SubModelID INT, 
Params VARCHAR (50),    
Note VARCHAR (50)
)
INSERT @TABLE

SELECT 2,   1,  'A',    2,  4935,   5560,'' ,   'check1',   'M14 x 1.50 Thread' UNION ALL

SELECT 2,   1,  'A',    2,  4935,   5560,'' ,   'check2',   'Package Quantity: 5'  UNION ALL

SELECT 2,   1,  'A',    2,  4935,   5569,'',        ''  ,'Dorman - AutoGrade - Boxed'

Expected Output

 2  1   A   2   4935    5560    0   check1;check2   M14 x 1.50 Thread;Package Quantity: 5

 2  1   A   2   4935    5569    0                  Dorman - AutoGrade - Boxed

This isn't particularly easy to do in SQL Server. Furthermore, this is essentially denormalizing your database table. Why do you want to do this? — Tim Biegeleisen, Jan 05 '16 at 07:37
@TimBiegeleisen: my records count are huge in millions and, except params and note, values in other column are same so i am trying to reduced the record count, stuffing params and note when value in all other column same. — Rajee Kasthuri, Jan 05 '16 at 07:46
Possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) — trincot, Jan 05 '16 at 08:30

score 1 · Accepted Answer · edited May 23 '17 at 12:23

Something like this should do the trick:

;WITH DataSource AS
(
    -- columns on which we are grouping
    SELECT DISTINCT [AppID]
                   ,[FileID]
                   ,[Action]
                   ,[ID]
                   ,[BaseVehicleID]
                   ,[ParttypeID]
                   ,[SubModelID]
    FROM @TABLE
)
SELECT *
FROM DataSource DS
CROSS APPLY
(
    SELECT STUFF
      (
          (
            SELECT DISTINCT ';' +[Params]
            FROM @TABLE T
            WHERE T.[AppID] = DS.[AppID]
                AND T.[FileID] = DS.[FileID]
                AND T.[Action] = DS.[Action]
                AND T.[ID] = T.[ID]
                AND T.[BaseVehicleID] = DS.[BaseVehicleID]
                AND T.[ParttypeID] = DS.[ParttypeID]
                AND T.[SubModelID] = DS.[SubModelID]
                AND [Params] <> ''
                FOR XML PATH(''), TYPE
          ).value('.', 'NVARCHAR(MAX)') 
          ,1
          ,1
          ,''
       )

) DS1([Params])
CROSS APPLY
(
    SELECT STUFF
      (
          (
            SELECT DISTINCT ';' +[Note]
            FROM @TABLE T
            WHERE T.[AppID] = DS.[AppID]
                AND T.[FileID] = DS.[FileID]
                AND T.[Action] = DS.[Action]
                AND T.[ID] = T.[ID]
                AND T.[BaseVehicleID] = DS.[BaseVehicleID]
                AND T.[ParttypeID] = DS.[ParttypeID]
                AND T.[SubModelID] = DS.[SubModelID]
                FOR XML PATH(''), TYPE
          ).value('.', 'NVARCHAR(MAX)') 
          ,1
          ,1
          ,''
       )
) DS2 ([Note])

Note, that this can become even more complex if you have many columns to group by. There are not T-SQL native implementation for aggregate concatenation function, but you can implement such if you want using SQL CLR. This answer contains everything you need. If you implement such function, the solution will look like this:

score 0 · Answer 2 · answered Jan 05 '16 at 08:05

DECLARE @TABLE TABLE
(
AppID INT,  FileID INT,Action CHAR (1),    ID INT, BaseVehicleID INT,  ParttypeID INT, SubModelID INT, Params VARCHAR (50),    
Note VARCHAR (50))

INSERT @TABLE

SELECT 2,   1,  'A',    2,  4935,   5560,'' ,   'check1',   'M14 x 1.50 Thread' UNION ALl
SELECT 2,   1,  'A',    2,  4935,   5560,'' ,   'check2',   'Package Quantity: 5'  UNION ALl
SELECT 2,   1,  'A',    2,  4935,   5560,'',        ''  ,'Dorman - AutoGrade - Boxed' 

select *from @table



select AppID, FileID, [Action],ID, BaseVehicleID, ParttypeID, SubModelID,
STUFF(
           (SELECT ';' + Params
           from @TABLE
           group by AppID, FileID, [Action],ID, BaseVehicleID, ParttypeID, SubModelID,Params
           FOR XML PATH('')),1,2,'') AS Params,
           STUFF(
           (SELECT ';' + Note
           from @TABLE
           group by AppID, FileID, [Action],ID, BaseVehicleID, ParttypeID, SubModelID,Note
           FOR XML PATH('')),1,1,'') AS Note

from @table 
group by AppID, FileID, [Action],ID, BaseVehicleID, ParttypeID, SubModelID

Stuffing data of one column when data in all other column same

2 Answers2