Posted by Caspian on 11/27/06 11:34

Dear All,

I'm attempting to create a query that will transpose repeated fields
into a single table structure. Can anyone think of how this can be done
as I'm stumped at the minute? I'd like to do this without having to
create a cursor due to the overheads and performance issues associated
with cursors. The table may also include additional fields which I'm
not interested in.

Serial Data is like this.............

Ikey Ival
----------------- ------------------------------
RAF_EMAIL testemail1@hotmail.com
RAF_FIRSTNAME testFirstName1
RAF_LASTNAME testLastname1
RAF_EMAIL testemail2@hotmail.com
RAF_FIRSTNAME testFirstName2
RAF_LASTNAME testLastname2
....

Transposed into table like this ..............

Email Firstname Lastname
-------------------- -------------------------- ----------------------------
testemail1@hotmail.com testFirstName1 testLastname1
testemail2@hotmail.com testFirstName2 testLastname2
....

Any help, much appreciated ...

Kind Regards,

Tim

-------------------------------------------------------------------------------------

NOTE: these create temporary tables ....

DECLARE @XML TABLE
(
ikey VARCHAR(200),
ival VARCHAR(1000)
)

INSERT INTO @XML
SELECT 'RAF_EMAIL' , 'testemail1@hotmail.com'
UNION ALL SELECT 'RAF_FIRSTNAME' , 'testFirstName1'
UNION ALL SELECT 'RAF_LASTNAME' , 'testLastname1'
UNION ALL SELECT 'RAF_EMAIL' , 'testemail2@hotmail.com'
UNION ALL SELECT 'RAF_FIRSTNAME' , 'testFirstName2'
UNION ALL SELECT 'RAF_LASTNAME' , 'testLastname2'
UNION ALL SELECT 'FORM_CATEGORY' , 'nothing'
UNION ALL SELECT 'NO_DOGS' , '1'


DECLARE @RESULTS
(
EMAIL,
FIRSTNAME,
LASTNAME
)

• Next in forum: Re: Manual back of database error
• Prev in forum: Re: Maximum number of Tables - is 4^15 big a number?
• Thread view: Transposing repetitive serial fields into Table structure

[Reply to this message]