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 (AT) hotmail (DOT) com
RAF_FIRSTNAME testFirstName1
RAF_LASTNAME testLastname1
RAF_EMAIL testemail2 (AT) hotmail (DOT) com
RAF_FIRSTNAME testFirstName2
RAF_LASTNAME testLastname2
....

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

Email Firstname Lastname
-------------------- -------------------------- ----------------------------
testemail1 (AT) hotmail (DOT) com testFirstName1 testLastname1
testemail2 (AT) hotmail (DOT) 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 (AT) hotmail (DOT) com'
UNION ALL SELECT 'RAF_FIRSTNAME' , 'testFirstName1'
UNION ALL SELECT 'RAF_LASTNAME' , 'testLastname1'
UNION ALL SELECT 'RAF_EMAIL' , 'testemail2 (AT) hotmail (DOT) 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
)

Caspian (timothy.odonnell (AT) hotmail (DOT) com) writes:
This is not possible with the data as given. There is no way to know
which RAF_EMAIL that goes with which RAF_FIRSTNAME. Recall that a
table is an unordere set of data. What you see on a piece of paper
is entirely irrelevant.

If you have an id that groups the related rows, it's a different matter:

SELECT Email = MIN (CASE Ikey WHEN 'RAF_EMAIL' THEN Ival END),
Firstname = MIN (CASE Ikey WHEN 'RAF_FIRSTNAME' THEN Ival END),
Lastname = MIN (CASE Ikey WHEN 'RAF_LASSTNAME' THEN Ival END)
FROM tbl
GROUP BY id


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx