Solved

# transpose rows

Posted on 2012-04-02
371 Views
Hi,
I have table which looks like

table1
wc_id email
101    aa@yahoo.com
101    bb@yahoo.com
102    cc@yahoo.com
203    dd@yahoo.com

i want the results as
wc_id email
101    aa@yahoo.com;bb@yahoo.com
102    cc@yahoo.com
203    dd@yahoo.com

Thanks
0
Question by:sam2929
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 45

Expert Comment

ID: 37797250
Hi Sam,

This kind of question comes up quite a bit.  :)   I can be solved pretty easily with a function, stored procedure, recursive query, or sometimes just a simple join.

Quick question.  Is there a limit on how many email addresses that can be assigned to any wc_id value?  If it's one and there is a primary (or unique) key, a simple join is probably the easiest.  If the limit is more than 1, recursive SQL is worth trying, and if there can be "a lot", you'll need to determine if you want to continue lines or put "a lot" of email addresses on one line.

Kent
0

LVL 18

Accepted Solution

Dave Ford earned 500 total points
ID: 37797268
The following example should show you how to do it pretty simply. Adapt as you see fit.

HTH,
DaveSlash

``````select *
from rec

SNUM         WORDNUM   WORD
1               1   This
1               2   is
1               3   a
1               4   fine
1               5   example
2               1   Yet
2               2   another
2               3   example
2               4   blah1
2               5   blah2
2               6   blah3

WITH rquery (snum, wordnum, sentence) as (
SELECT base.snum, base.wordnum, base.word
FROM rec base
WHERE wordnum = 1

UNION ALL

SELECT t1.snum, t1.wordnum, sentence || ' ' || t1.word
FROM rquery t0
join rec t1
on t0. snum = t1. snum
AND t0.wordnum + 1 = t1.wordnum
)
select r1.SNUM,
r1.SENTENCE
from rquery r1
where r1.wordnum = (
select max(r2.wordnum)
from rquery r2
where r2.snum = r1.snum)

SNUM   SENTENCE
1   This is a fine example
2   Yet another example blah1 blah2 blah3
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Exposing an EAV database model as a mart for visualisation 9 134
AS400 Single Sign On 3 1,490
Populating a generated column that has been added 5 90
Shell Script on AIX 7 173
November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.
###### Suggested Courses
Course of the Month8 days, 7 hours left to enroll