0

I have 4 tables that have 2 columns each.

'Column 1' is the 'One' 'Column 2' is the 'Many'

Column 1 has several different ID's that i need to group somehow and build a delimited string of all the 'Columns 2' values.

I need to do this for every distinct 'Column 1' value.... is this possible?

So for example i have this table..

DECLARE @tblDeadsData TABLE (
    [ID] INT IDENTITY(1,1) NOT NULL,
    [ContainerID] INT NULL,
    [DeadsID] INT NULL
    )

It is already populated with data, i need to build a delimited string of ALL the [DeadsID] for each [ContainerID], and then these delimited string need to be placed into this table (the DeadsDataTable Data goes into the tblLastMerge.DeadsIDList in this case)..

CREATE TABLE tblLastMerge(
    [ID] INT IDENTITY(1,1) NOT NULL,
    [FeedLotID] INT NULL,
    [ContainerID] INT NULL,
    [ContainerName] VARCHAR(40) NULL,
    [IsMergeTargetContainer] BIT NULL,
    [PurchaseIDList] VARCHAR(1000) NULL,
    [DeadsIDList] VARCHAR(1000) NULL,
    [RailersIDList] VARCHAR(1000) NULL,
    [FeedBillIDList] VARCHAR(1000) NULL
    )

***************************EDIT********************************* in regards to the duplicate post.........Concatenation is not delimited!!!!!! no wonder when i did a search for my problem i did not see this post..... i do think you should link these 2 post though as i found an answer much more simplified compared to the (supposedly) original post

Paul S
  • 190
  • 1
  • 1
  • 11
  • This question is not readable. Please clean up the formatting. – Tim Biegeleisen Jun 02 '16 at 05:42
  • Can you please add some table values and expected output? – Mocas Jun 02 '16 at 05:43
  • 1
    Do not post your code snapshots instead post the code..! – Umair Shah Jun 02 '16 at 05:48
  • Added `sql-server` and `t-sql` tags based on the code given –  Jun 02 '16 at 06:02
  • values and output ...... i want a delimited list of column [DeadsID] separated (grouped by if you will) by [ContainerID]. just that much would be progress for now :) – Paul S Jun 02 '16 at 06:05
  • i can currently get the delimited string of ALL of [DeadsID] using this code select cast([DeadsID] as varchar) + ',' from @tbldeadsdata for xml path('') but i need multiple strings returned if there is more than 1 [ContainerID]...1 for each container – Paul S Jun 02 '16 at 06:14

1 Answers1

0

Thanks...... i found an answer

--table to hold the old deads data DECLARE @tblDeadsData TABLE ( [ID] INT IDENTITY(1,1) NOT NULL, [ContainerID] VARCHAR(10) NULL, [DeadsID] VARCHAR(10) NULL )

insert into @tbldeadsdata
    values('1','2'),('1','3'),('1','4'),('2','2'),('2','3'),('2','4')


SELECT ContainerID, DeadsID = STUFF((SELECT N', ' + DeadsID 
  FROM @tblDeadsData AS p2
   WHERE p2.ContainerID = p.ContainerID 
   ORDER BY DeadsID
   FOR XML PATH(N'')), 1, 2, N'')
FROM @tblDeadsData AS p
GROUP BY ContainerID
ORDER BY ContainerID
Paul S
  • 190
  • 1
  • 1
  • 11