Need help for Cross Tab SQL Query

Question

This is the source example table:

    UserID  UserNameType    UserName
    1       First Name      FN1
    1       Last Name       LN1
    2       First Name      FN2
    2       Last Name       LN2
    3       First Name      FN3
    3       Last Name       LN3
    4       First Name      FN4
    4       Last Name       LN4

I would like to have a SQL query to display my result like the given format below.

   
    UserID  FirstName   LastName
    1       FN1         LN1
    2       FN2         LN2
    3       FN3         LN3
    4       FN4         LN4

Need a little more help on this...

Thanks, Yugal

Answer 1

A simple cross-tab method (which I personally prefer) is to use the group by operator with MAX(CASE WHEN...) pattern to control the cross-tab fields. This is easy to understand IMO and easy to extend with more fields. I'm not sure about the performance.

select
    UserID,
    max(case when usernametype='First Name' then username end) as FirstName,
    max(case when usernametype='Last Name' then username end) as LastName
from
    MyTable T
group by
    UserID