merge 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 2005 Forums

Transact-SQL (2005)

merge records

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-04-28 : 15:21:58

How do i merge data like below table structure, pls see the output result.

Declare @tbl1 table
(id int,
Col1 varchar(50),
Col2 varchar(50),
Col3 varchar (50)
)

INSERT INTO @tbl1
SELECT 1,'A001','Y',NULL

INSERT INTO @tbl1
SELECT 2,'A002','N',NULL

INSERT INTO @tbl1
SELECT 3,'A003','N',NULL

INSERT INTO @tbl1
SELECT 4,'A004','Y',NULL

INSERT INTO @tbl1
SELECT 5,'A005','N',NULL

INSERT INTO @tbl1
SELECT 6,'A006','N',NULL

INSERT INTO @tbl1
SELECT 7,'A007','N',NULL

INSERT INTO @tbl1
SELECT 8,'A008','Y',NULL

select * from @tbl1

id Col1 Col2 Col3
1 A001 Y A002A003
2 A002 N
3 A003 N
4 A004 Y A005A006A007
5 A005 N
6 A006 N
7 A007 N
8 A008 Y

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-04-29 : 12:03:19

anybody have an idea?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-29 : 13:25:34

[code]SELECT
t1.id,
t1.col1,
t1.col2,
case when t1.col2 = 'Y' then t3.col3 END AS Col3
FROM
@tbl1 t1
CROSS APPLY
( SELECT TOP 1 id FROM @tbl1 t2 WHERE t2.id > t1.id AND t2.col2 = 'Y' ORDER BY t2.id) t2
CROSS APPLY
( SELECT
( SELECT c.col1 AS [text()]
FROM @tbl1 c
WHERE c.id < t2.id and c.id >= t1.id AND c.col2 != 'Y'
FOR XML PATH(''),type
).value('.','varchar(max)') AS col3
) t3[/code]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-29 : 13:31:10

Soon he will ask you to write the update statement for him.

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-29 : 13:49:46

Nice coding by the way! But now you got some 'splaining to do. I've seen the XML path used to concatenate rows before, but not quite like this! Could you tell me just what this is doing?

SELECT
( SELECT c.col1 AS [text()] <<-- never seen that before
FROM @tbl1 c
WHERE c.id < t2.id and c.id >= t1.id AND c.col2 != 'Y'
FOR XML PATH(''),type <<-- or this (type)

).value('.','varchar(max)') AS col3 <<-- or this in this context

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-29 : 15:19:12

Yes Ricky...

and thank you.

1. text() is an xpath function which just says, take the string value. In this case, of course, the column could have been aliased to anything at all because of the processing that happens later.

2. Normally the output from XML path is nvarchar. The TYPE directive changes that to XML so it can be used as XML in the outer query.

3. The ".value('.','varchar(max)')" is not really required in this case, but if the strings you were trying to concatenate had some of those reserved characters such as <, >, & etc., they would get entitized. The value function takes the string value of the XML. You will see what I mean if you run this code, where you will see the "&"

Declare @tbl1 table
(id int,
Col1 varchar(50),
Col2 varchar(50),
Col3 varchar (50)
)


INSERT INTO @tbl1
SELECT 1,'A&001','Y',NULL

INSERT INTO @tbl1
SELECT 2,'A&002','N',NULL

INSERT INTO @tbl1
SELECT 4,'A004','Y',NULL

SELECT
   t1.id,
   t1.col1,
   t1.col2,
   case when t1.col2 = 'Y' then t3.col3 END AS Col3
FROM
   @tbl1 t1
   CROSS APPLY
   ( SELECT TOP 1 id FROM @tbl1 t2 WHERE t2.id > t1.id AND t2.col2 = 'Y' ORDER BY t2.id) t2 
   CROSS APPLY
   (   SELECT 
      CAST((   SELECT c.col1 AS [text()]
         FROM @tbl1 c 
         WHERE c.id < t2.id and c.id >= t1.id AND c.col2 != 'Y'
         FOR XML PATH('')
      ) AS VARCHAR(MAX)) AS col3
   ) t3

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-29 : 15:28:24

Awesome! This

quote:

2. Normally the output from XML path is nvarchar. The TYPE directive changes that to XML so it can be used as XML in the outer query.

just blew a whole bunch of cobwebs out of my mind! I've had to deal with xml on a limited basis, but it looks like I only thought it was xml. Hey, it looked like XML! There's a lot of code I've copied and pasted from others trying to understand how to use xml, expecially with cross apply, that is now starting to make a lot moer sense.

Thanks again, Sunita, you rock!

Jim

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-29 : 16:41:35

Heh! Jim!! Thanks for the kind words. Everything I pretend to know about SQL XML, I learned from here: http://www.amazon.com/Pro-SQL-Server-2008-XML/dp/1590599837

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-05-02 : 15:24:45

thanks sunitabeck and jimf, how do we seperate each id by ;

1 A001 Y A002;A003
4 A004 Y A005;A006;A007

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-02 : 17:43:14

[code]
....
CROSS APPLY
( SELECT STUFF(
CAST(( SELECT
';' as [text()],
c.col1 AS [text()]
FROM @tbl1 c
WHERE c.id < t2.id and c.id >= t1.id AND c.col2 != 'Y'
FOR XML PATH('')
) AS VARCHAR(MAX)),1,1,'') AS col3
) t3
[/code]
Also, you may want to change the two instances of "CROSS APPLY" to "OUTER APPLY". Without the very last row would not get picked up.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2011-05-03 : 11:47:56

perfect, you the man.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources