November 21, 2008 at 10:11 pm
[font="Arial"]
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.....
[/font]
November 22, 2008 at 11:20 am
A few things:
1. Please refer to the article in my signature for how to post sample data in future posts. (Your sample data will end up looking like mine does at the top of this query)
2. I'm not sure of the relevance/importance of the ID column, so I didn't do the actual delete/insert part of this.
3. I added N's to the end of all of your fields because I didn't want to bracket all of them and you managed to use keywords for every field name :blink:.
The below query will give you the maximum ID value for each matching User/Date and a concatenated list of all their desc fields.
[font="Courier New"]DECLARE @A TABLE(
NoN INT,
DescN VARCHAR(20),
UserN VARCHAR(20),
DateTimeN DATETIME)
INSERT INTO @A(NoN, DescN, UserN, DatetimeN)
SELECT 1,'hello', 'AAA', '10/10/2008 10:20:30' UNION ALL
SELECT 2,'Test', 'BBB', '12/11/2008 12:14:30' UNION ALL
SELECT 3,'qwert', 'AAA', '10/10/2008 10:20:30'
SELECT UserN, DatetimeN, MAX(A.NoN) MNoN,
STUFF((SELECT
DescN
FROM
@A A2
WHERE A2.UserN = A.UserN AND A2.DatetimeN = A.DatetimeN
FOR XML PATH ('')), 1,2,'') DescNC
FROM @A A
GROUP BY UserN, DatetimeN[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply