Merging records - 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 2000 Forums

Transact-SQL (2000)

Merging records

Author

Topic

KabirPatel
Yak Posting Veteran

54 Posts

Posted - 2008-01-23 : 05:09:09

Hi,

Consider the following data in a table:

TableID | TableTitle | Roles | PackageID | Desc
-------------------------------------------------------
TEST01 A test one P1 Merge
TEST01 A test null P1 Merge

I want to extract the data from this table so that whenever the "desc" field has a value of "Merge" we only see the following:

TableID | TableTitle | Roles | PackageID | Desc
-------------------------------------------------------
TEST01 A test one P1 Merge

I could either do this by deleting the second record in the table or by using a select statement

Can somebody help?

Thanks in advance.
Kabir

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-23 : 05:16:39

will this be the pattern always? that is first record with all values and second one with only Roles null? then use this:-

SELECT * FROM Table where Roles IS NOT NULL AND Desc<>'Merge'

KabirPatel
Yak Posting Veteran

54 Posts

Posted - 2008-01-23 : 06:27:27

Sorry - I should have clarified further....

Consider the following:

TableID | TableTitle | Roles | PackageID | Desc
-------------------------------------------------------
TEST01 A test one P1 Merge
TEST01 A test null P1 Merge
TEST01 A test two P2 Merge
TEST01 A test null P3 Merge

In this instance I would like to see the following:

TableID | TableTitle | Roles | PackageID | Desc
-------------------------------------------------------
TEST01 A test one P1 Merge
TEST01 A test two P2 Merge
TEST01 A test null P3 Merge

In other words whenever a duplicate TableID, PackageID combination exists I would only like to see the one which has a Role value. In all other instances I would like to see all the data.

Cheers,
Kabir

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-23 : 07:06:54

[code]-- Prepare sample data
DECLARE @Sample TABLE (TableID CHAR(6), TableTitle CHAR(6), Roles CHAR(3), PackageID CHAR(2), Description CHAR(5))

INSERT @Sample
SELECT 'TEST01', 'A test', 'one', 'P1', 'Merge' UNION ALL
SELECT 'TEST01', 'A test', null, 'P1', 'Merge' UNION ALL
SELECT 'TEST01', 'A test', 'two', 'P2', 'Merge' UNION ALL
SELECT 'TEST01', 'A test', null, 'P3', 'Merge'

-- Show the expected output
SELECT TableID,
TableTitle,
MAX(Roles) AS Roles,
PackageID,
Description
FROM @Sample
GROUP BY TableID,
TableTitle,
PackageID,
Description[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-23 : 07:13:02

[code]DECLARE @t Table
(
TableID varchar(10),
TableTitle char(10),
Roles varchar(10),
PackageID varchar(5),
[Desc] varchar(10))
-------------------------------------------------------
INSERT INTO @t values('TEST01', 'A test', 'one' ,'P1','Merge')
INSERT INTO @t values('TEST01', 'A test', null, 'P1', 'Merge')
INSERT INTO @t values('TEST01', 'A test', 'two', 'P2', 'Merge')
INSERT INTO @t values('TEST01', 'A test', null, 'P3', 'Merge')
INSERT INTO @t values('TEST01', 'A test', null, 'P2', 'Merge')
INSERT INTO @t values('TEST01', 'A test', null, 'P4', 'Merge')
INSERT INTO @t values('TEST01', 'A test', null, 'P5', 'Merge')
INSERT INTO @t values('TEST01', 'A test', 'five', 'P5', 'Merge')

SELECT t1.* FROM @t t1
INNER JOIN
(SELECT PackageID,MAX(Roles) AS Roles FROM @t GROUP BY PackageID)t
ON t.PackageID=t1.PackageID
ANd ISNULL(t.Roles,'')=ISNULL(t1.Roles,'')

output
------------------
TableID TableTitle Roles PackageID Desc
---------- ---------- ---------- --------- ----------
TEST01 A test one P1 Merge
TEST01 A test two P2 Merge
TEST01 A test NULL P3 Merge
TEST01 A test NULL P4 Merge
TEST01 A test five P5 Merge[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources