|
|
Posted by Erland Sommarskog on 11/27/06 22:44
Caspian (timothy.odonnell@hotmail.com) writes:
> 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
> ...
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@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|