Relational Output - 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

General SQL Server Forums

New to SQL Server Programming

Relational Output

Author

Topic

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-08-27 : 03:28:53

Hi I have two tables

Table1
======================
id name class
01 John one
02 mary two
10 peter five

Table2
=======================
doc_id doc_rel doc_name
01 1 TOP
02 1-400001 D
03 1-400001-400002 C
04 1-400001-400138-400139 A
05 1-400013 E
06 1-400013-400014 B
07 1-400013-400144 J
08 1-400023 G
09 1-400023-400024 K
10 1-400023-400024-400150 I
11 1-400023-400025 A
12 1-400026 H
13 1-400026-400027 F

I run the follwoing query :

SELECT a.id, a.name, b.rel_id
from table1 a
left outer join table2 b on a.id = b.doc_id

OUTPUT is given below :
id name doc_id
01 john 1
02 mary 1-400001
10 peter 1-4000023-4000024-400150
I would like the output to be shown as

id name doc_id
01 john TOP
02 mary 1-D
10 peter 1-G-K-I

If anyone can help that wud be great..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-27 : 09:44:06

[code]SELECT t1.id,t1.name,LEFT(dl.docrellist,LEN(dl.docrellist)-1)
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.doc_id=t1.id
CROSS APPLY (SELECT doc_name + '-'
FROM Table2
WHERE SUBSTRING(t2.doc_rel,2,LEN(t2.doc_rel)-1) + '-' LIKE SUBSTRING(doc_rel,2,LEN(doc_rel)-1) + '-%'
FOR XML PATH(''))dl(docrellist)
[/code]

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-08-27 : 23:52:48

That was bullseye Viksah. Thank you much.

There is just one minot problem in the output

If the doc_name has an ampersand "&"e.g. if doc name is "You & Me"
The output is shown as

"You & amp; Me"

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-08-28 : 03:41:14

I Figured it out. Done a replace of characters. Its working great now. Thanks !!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources