asked on

Updating the tables and its records based upon the list of IDS in a column

I have the query below where

SELECT message, type, count(1) as total, 
       STUFF( (SELECT N',' + CONVERT(NVCHAR(MAX), id)
               FROM dbo.testing t2
               WHERE t2.message = t.message and t2.type = t.type
               FOR XML PATH(''), TYPE
              ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
            )
FROM testing t
GROUP BY message,type 
HAVING count(1) > 1;

Open in new window

i want to populate a field called eid starting from 1 to the number of records i have and each record have 1 or 5 or 10 ids which we just figured out lik: if the ids column have 5 ids like 18,19,20,21,22 for all these the eid will be 1

like this screenshot

http://prntscr.com/m4w5r5

i want to have the errorid first start from 1000 and for every row, it should increment but for every row, there are associated IDS which are in comma separated values, they all should have the same errorid for that row

Dorababu M

Try as below

CREATE TABLE your_table
	([ID] int, [ReportId] int, [Email] varchar(7))
;
	
INSERT INTO your_table
	([ID], [ReportId], [Email])
VALUES
	(1, 1, 'a@a.com'),
	(2, 2, 'b@b.com'),
	(3, 1, 'c@c.com'),
	(4, 3, 'd@d.com'),
	(5, 3, 'e@e.com');
	
	SELECT ReportId, Email = 
    STUFF((SELECT ', ' + Email
           FROM your_table b 
           WHERE b.ReportId = a.ReportId 
          FOR XML PATH('')), 1, 2, '')
FROM your_table a
GROUP BY ReportId

Open in new window

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=55ef5ce3d7121e45ed56ca1a56fb3911

Dorababu M

Updated one with count as you required

CREATE TABLE your_table
	([ID] int, [ReportId] int, [Email] varchar(7))
;
	
INSERT INTO your_table
	([ID], [ReportId], [Email])
VALUES
	(1, 1, 'a@a.com'),
	(2, 2, 'b@b.com'),
	(3, 1, 'c@c.com'),
	(4, 3, 'd@d.com'),
	(5, 3, 'e@e.com');
	
	SELECT ReportId, count(1) as total, Email = 
    STUFF((SELECT ', ' + Email
           FROM your_table b 
           WHERE b.ReportId = a.ReportId 
          FOR XML PATH('')), 1, 2, '')
FROM your_table a
GROUP BY ReportId

Open in new window

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=567c81d2e4f33a0fa10e2fc5be0c9c02

Coast Line

ASKER

i need to update instead of Inserting and your fiddle is bit confusing me, can you use the same structure to show what i shared in the screenshot

Coast Line

ASKER

i am sure you did not understood the question

ASKER CERTIFIED SOLUTION

D B

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Coast Line

ASKER

almost correct, if you see y screnshot, the column also has IDS, i created a pair of ids just to ease the process that if the IDS which are 12,13,14,15 and so on, they should have one common errorid which could be starting value or an incremental value based upon on which row they fell on.

D B

But the original table has individual rows, one per ID.
Originality you'd have:

                        database                              5
                        database                              6
                        database                              7
                        database                              8
                        database                              9
Access Denied           org.lucee.resource.s3.S3Exception  2733
Access Denied           org.lucee.resource.s3.S3Exception  2734

Open in new window

Are you wanting to provide a unique errorid to the original (above) code, or to the result in your screenshot?
If so, add ROW_NUMBER() OVER (ORDER BY message, type) + 999 AS errorid,
to your select statement:

SELECT ROW_NUMBER() OVER (ORDER BY message, type) + 999 AS errorid,
		message, type, count(1) as total, 
       STUFF( (SELECT N',' + CONVERT(NVARCHAR(MAX), id)
               FROM dbo.testing t2
               WHERE t2.message = t.message and t2.type = t.type
               FOR XML PATH(''), TYPE
              ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
            )
FROM dbo.testing t
GROUP BY message,type 
HAVING count(1) > 1;

Open in new window

D B

If this is not correct, provide a sample of what you want the actual data to look like when you run your query. Do you want the errorid in the testing table or as part of the output of your query?

Dorababu M

Have you replaced the columns and data as per your requirement and tried?

Coast Line

ASKER

Thanks