Merging Rows of a Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Merging Rows of a Table

Author

Topic

kneekill
Yak Posting Veteran

76 Posts

Posted - 2008-11-20 : 12:53:29

Hi

I need to merge some rows in a table into one single row based on few criteria.

example: Table


______________________________________________________________

S.No          Desc         User        DateTime
______________________________________________________________

  1           hello         AAA         10/10/2008 10:20:30   
______________________________________________________________

  2           Test          BBB         12/11/2008 12:14:30   
______________________________________________________________

  3           qwert         AAA         10/10/2008 10:20:30   
______________________________________________________________

In the Above table if the "User" and "Date Time" column values are equal then i need to merge all the rows into one single row and delete the rows used for merging.By the way "S.No" is an auto incremental primary key column.

My Result Table should as below


______________________________________________________________
S.No             Desc         User         DateTime
______________________________________________________________

  2              Test          BBB         12/11/2008 12:14:30   
______________________________________________________________
                 
                 hello
  4              qwert         AAA         10/10/2008 10:20:30   
______________________________________________________________

Waiting for your help to accomplish this.....

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-20 : 14:07:32

Try this:


insert into <TABLE> ([user], dt, [desc]) 
select distinct [user], dt, cl.[desc]
from <TABLE> t1
cross apply (
select [desc]+char(10)  from <TABLE> s where exists (
select 1 from (
select [user],dt from <TABLE>
group by [user], dt having count(*) > 1)t 
where [user] = s.[user] and dt = s.dt
) and [user] = t1.[user] and dt = t1.dt 
for xml path('')
)cl([desc])

delete from <TABLE> where sno in (
select sno from
<TABLE> s
where exists (select 1 from (select [user], dt from <TABLE> group by [user], dt having count(*) > 1)t 
where [user] = s.[user] and dt = s.dt )
and right([desc],1) <> char(10))

select * from <TABLE>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 02:25:37

[code]INSERT INTO Table
SELECT List,
User,
DateTime
FROM
(
SELECT DISTINCT SLNo,User,DateTime,
STUFF((SELECT CHAR(13)+Desc FROM YourTable WHERE User=t.User
AND DateTime=t.DateTime
FOR XML PATH('')),1,1,'') AS List,
COUNT(SLNo) OVER (PARTITION BY User,DateTime) AS Total
FROM YourTable t
)r
WHERE Total>1

DELETE t
FROM
(
SELECT
COUNT(SLNo) OVER (PARTITION BY User,DateTime) AS Total
FROM YourTable

)t
WHERE Total>1[/code]

kneekill
Yak Posting Veteran

76 Posts

Posted - 2008-11-21 : 05:48:17

hanbingl..! thanx for the solution i have a few problems though using it
below is the result after trying your solution...

first table before running your query


14	a	bbb	2008-12-21 04:20:00
2	b	qwe	2008-12-21 04:20:00
15	asda	bbb	2008-12-21 04:20:00

result after the insert


14	a	bbb	2008-12-21 04:20:00
2	b	qwe	2008-12-21 04:20:00
15	asda	bbb	2008-12-21 04:20:00
22    a asda  bbb	2008-12-21 04:20:00
23	NULL	    qwe	2008-12-21 04:20:00

final result after the insert and delete


22	a asda 	bbb	2008-12-21 04:20:00
23	NULL	qwe	2008-12-21 04:20:00

the merging is going on good,, but for example if we have same "datetime" data for columns updated by other users then the "desc" column of other users is getting nullified and also geeting deleted and being reinserted.


2	b	qwe	2008-12-21 04:20:00

is the original row which is inserted by user "qwe" has same "datetime" as the rows inserted by "bbb".this row is getting deleted and re-inserted with null "desc" column


23	NULL	qwe	2008-12-21 04:20:00

And could you also help me how tho use the same query if i need to merge more than one "desc" column.

quote:

Originally posted by hanbingl

Try this:


insert into <TABLE> ([user], dt, [desc]) 
select distinct [user], dt, cl.[desc]
from <TABLE> t1
cross apply (
select [desc]+char(10)  from <TABLE> s where exists (
select 1 from (
select [user],dt from <TABLE>
group by [user], dt having count(*) > 1)t 
where [user] = s.[user] and dt = s.dt
) and [user] = t1.[user] and dt = t1.dt 
for xml path('')
)cl([desc])

delete from <TABLE> where sno in (
select sno from
<TABLE> s
where exists (select 1 from (select [user], dt from <TABLE> group by [user], dt having count(*) > 1)t 
where [user] = s.[user] and dt = s.dt )
and right([desc],1) <> char(10))

select * from <TABLE>

kneekill
Yak Posting Veteran

76 Posts

Posted - 2008-11-21 : 06:04:01

visakh16..! thanx to u too for the solution. but i am not able to make your solution work for me

first table before running your query


24	tyu	aaa	2008-12-21 04:20:00
25	cvb	aaa	2008-12-21 04:20:00
23	xyz	qwe	2008-12-21 04:20:00

result after the insert


24  tyu	     aaa	   2008-12-21 04:20:00
25  cvb	     aaa	   2008-12-21 04:20:00
26  cvb           aaa	       2008-12-21 04:20:00
27  cvb           aaa	       2008-12-21 04:20:00
23  xyz	     qwe	   2008-12-21 04:20:00

final result after the insert and delete


23	xyz	qwe	2008-12-21 04:20:00

all the rows with same "user" and "datetime" are getting deleted after the insert and delete.
except for the mismatching rows.

Note: Aslo some improper data is being displayed after the the insert in the "desc" column like

"#x0D;tyu#x0D;cvb" for "cvb"

quote:

Originally posted by visakh16

INSERT INTO Table
SELECT List,
User,
DateTime
FROM
(
SELECT DISTINCT SLNo,User,DateTime,
STUFF((SELECT CHAR(13)+Desc FROM YourTable WHERE  User=t.User
AND DateTime=t.DateTime
FOR XML PATH('')),1,1,'') AS List,
COUNT(SLNo) OVER (PARTITION BY User,DateTime) AS Total
FROM YourTable t
)r
WHERE Total>1

DELETE t
FROM
(
SELECT 
COUNT(SLNo) OVER (PARTITION BY User,DateTime) AS Total
FROM YourTable 

)t
WHERE Total>1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 13:00:30

[code]
DECLARE @DELETED_ITEMS TABLE
(
Desc varchar(100),
User varchar(100),
DateTime datetime
)

DELETE t
OUTPUT DELETED.Desc,
DELETED.User,
DELETED.DateTime
INTO @DELETED_ITEMS
FROM
(SELECT COUNT(SLNo) OVER (PARTITION BY User,DateTime) AS Total
FROM YourTable)t
WHERE t.Total>1

INSERT INTO YourTable
(
Desc,
User,
DateTime
)
SELECT DISTINCT LEFT(dl.DescList,LEN(dl.DescList)-1),
d.User,
d.DateTime
FROM @DELETED_ITEMS d
CROSS APPLY (SELECT Desc + ','
FROM @DELETED_ITEMS
WHERE User=d.User
AND DateTime=d.DateTime
FOR XML PATH(''))dl(DescList)
[/code]

kneekill
Yak Posting Veteran

76 Posts

Posted - 2008-11-24 : 02:28:43

visakh16,

i am getting a problem in the delete section of the query

DELETE t
OUTPUT DELETED.Desc,
DELETED.User,
DELETED.DateTime
INTO @DELETED_ITEMS
FROM
(SELECT COUNT(SLNo) OVER (PARTITION BY User1,DateTime1) AS Total FROM table_1)t WHERE t.Total>1

i get the follwoing errors in delete part.
"Invalid column name 'Desc'."
"Invalid column name 'User'."
"Invalid column name 'DateTime'."

When i run the select part of delete - output clause
SELECT COUNT(SLNo) OVER (PARTITION BY User1,DateTime1) AS Total FROM table_1 t WHERE t.Total>1

i get "Invalid column name 'Total'."

quote:

Originally posted by visakh16


DECLARE @DELETED_ITEMS TABLE
(
Desc varchar(100),
User varchar(100),
DateTime datetime
)


DELETE t
OUTPUT DELETED.Desc,
DELETED.User,
DELETED.DateTime
INTO @DELETED_ITEMS
FROM
(SELECT COUNT(SLNo) OVER (PARTITION BY User,DateTime) AS Total
FROM YourTable)t
WHERE t.Total>1


INSERT INTO YourTable
(
Desc,
User,
DateTime
)
SELECT DISTINCT LEFT(dl.DescList,LEN(dl.DescList)-1),
d.User,
d.DateTime
FROM @DELETED_ITEMS d
CROSS APPLY (SELECT Desc + ','
             FROM @DELETED_ITEMS
             WHERE User=d.User
             AND DateTime=d.DateTime
             FOR XML PATH(''))dl(DescList)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 02:43:20

Forgot to include columns in derived table

DELETE t
OUTPUT DELETED.Desc,
DELETED.User,
DELETED.DateTime
INTO @DELETED_ITEMS
FROM
(SELECT COUNT(SLNo) OVER (PARTITION BY User1,DateTime1) AS Total ,
Desc,User,DateTime
FROM table_1)t WHERE t.Total>1

also select should be this as you cant use aliases directly in where

SELECT COUNT(SLNo) OVER (PARTITION BY User1,DateTime1) AS Total ,
Desc,User,DateTime
FROM table_1
WHERE  COUNT(SLNo) OVER (PARTITION BY User1,DateTime1)>1

kneekill
Yak Posting Veteran

76 Posts

Posted - 2008-11-24 : 05:39:44

Thanks a Lot visakh for your patience & time
And Thanks a ton for solving the problem and explaining it for a SQL n00b like me

quote:

Originally posted by visakh16

Forgot to include columns in derived table

DELETE t
OUTPUT DELETED.Desc,
DELETED.User,
DELETED.DateTime
INTO @DELETED_ITEMS
FROM
(SELECT COUNT(SLNo) OVER (PARTITION BY User1,DateTime1) AS Total ,
Desc,User,DateTime
FROM table_1)t WHERE t.Total>1

also select should be this as you cant use aliases directly in where

SELECT COUNT(SLNo) OVER (PARTITION BY User1,DateTime1) AS Total ,
Desc,User,DateTime
FROM table_1
WHERE  COUNT(SLNo) OVER (PARTITION BY User1,DateTime1)>1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 05:50:15

No problem

You're always welcome
Feel free to post your doubts

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources