query help - 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 2008 Forums

Transact-SQL (2008)

query help

Author

Topic

zahi_levi
Starting Member

1 Post

Posted - 2011-07-05 : 11:47:06

Hello all, I need help to merge tables,columns,rows in SELECT query.

first table : EVENTS
ID TAG TYPE
1 tag1 type1
2 tag2 type2
3 tag3 type3

second table: EVENTS_PROPS
ID NAME VALUE
1 name1-1 value1-1
1 name1-2 value1-2
1 name1-3 value1-3
2 name2-1 value2-1

I would like to merge those tables into one table as follow :

ID TAG TYPE MSG
1 tag1 type1 name1-1=value1-1;name1-2=value1-2;name1-3=value1-3
2 tag2 type2 name2-1=value2-1
3 tag3 type3

I will appreciate any help and even more if it will not be DB type dependent.(I'm using Oracle 10).

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-05 : 13:34:18

You can do it by making use of XML features like this:

SELECT
   e.id,
   e.tag,
   e.type,
   c.concatValues
FROM
   EVENTS e
   CROSS APPLY
   (
      SELECT STUFF(((
      SELECT
         ';'+NAME+'='+VALUE
      FROM
         EVENTS_PROPS ep
      WHERE
         ep.id = e.ID 
      FOR
         XML PATH(''),TYPE ).value('.','varchar(max)')),1,1,'')
   ) c(concatValues);

There are a few other ways - you may want to look up this blog:http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources