How to get a comma separated list from a column of data.

• o365

  SSC Journeyman

  Points: 79

  More actions

  June 12, 2018 at 1:34 pm

  #370705

  
IF OBJECT_ID('dbo.Ship326587') IS NOT NULL DROP TABLE [dbo].[Ship326587]
CREATE TABLE [dbo].[Ship326587](
[ShipperID] [varchar](10) NULL,
[InvtID] [varchar](10) NULL,
[CONTAINER_NO] [varchar](15) NULL,
[ITEM_CODE] [varchar](15) NULL,
[BOX_NO] [varchar](4) NULL,
[NET_WEIGHT] INT
)

  INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','713',325)
INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','714',487)
INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','715',325)
INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','716',487)
INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','717',325)
INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','718',488)
INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','719',311)
INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','720',324)
INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','721',343)
INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','722',324)
INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','723',443)
INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','724',342)
INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','725',453)
INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','726',324)
INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','727',367)
INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','728',384)
INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','729',384)
INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','730',387)
INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','731',432)
INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','732',323)
INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','733',334)
INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','734',335)
INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','735',343)
INSERT INTO [dbo].[Ship326587] (ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,BOX_NO,NET_WEIGHT) VALUES ('81832414','GIPH133711','EITU1154745','TCYUTP13371101','736',343)

  SELECT ShipperID,InvtID,CONTAINER_NO,ITEM_CODE,SUM(NET_WEIGHT) NetWgt,Box_List from [dbo].[Ship326587]
GROUP BY ShipperID,InvtID,CONTAINER_NO,ITEM_CODE

  Need to get the following results
  
ShipperID    InvtID          CONTAINER_NO       ITEM_CODE            NetWgt   Box_List
81832414     GIPH133711      EITU1154745        TCYUTP13371101       8933     713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736


• DesNorton

  SSC-Insane

  Points: 24284

  More actions

  June 12, 2018 at 1:46 pm

  #1993770

  This should get you the desired results
  SELECT
  src.ShipperID
, src.InvtID
, src.CONTAINER_NO
, src.ITEM_CODE
, NetWgt = SUM(src.NET_WEIGHT)
, BoxList = (
     STUFF((SELECT ',' + lst.Box_No
       FROM [dbo].[Ship326587] AS lst
       WHERE lst.ShipperID = src.ShipperID
        AND lst.InvtID = src.InvtID
        AND lst.CONTAINER_NO = src.CONTAINER_NO
        AND lst.ITEM_CODE = src.ITEM_CODE
       ORDER BY lst.Box_No
       FOR XML PATH('')
       ), 1, 1, '')
     )
FROM [dbo].[Ship326587] AS src
GROUP BY src.ShipperID, src.InvtID, src.CONTAINER_NO, src.ITEM_CODE;


  ---

  How to post data/code on a forum to get the best help.

• o365

  SSC Journeyman

  Points: 79

  More actions

  June 12, 2018 at 2:00 pm

  #1993773

  Perfect.

  Thanks