CODEWITHSUNDEEP
mysqlsqlsql-server-2005sql-server-2008
Yugal
Yugal

Reputation: 424

Need help for Cross Tab SQL Query

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...

Need Help

Thanks, Yugal

Upvotes: 3

Views: 765

Answers (5)

Pankaj
Pankaj

Reputation: 10115

Suppose you have following table

create table #temp1
(
    UserID  int,
    usernametype varchar(10),
    username varchar(10)
)

Following are the inserts

insert into #temp1(userid, usernametype, username) valueS(1, 'First Name', 'FN1')
insert into #temp1(userid, usernametype, username) valueS(1, 'Last Name', 'LN1')
insert into #temp1(userid, usernametype, username) valueS(2, 'First Name', 'FN2')
insert into #temp1(userid, usernametype, username) valueS(2, 'Last Name', 'LN2')
insert into #temp1(userid, usernametype, username) valueS(3, 'First Name', 'FN3')
insert into #temp1(userid, usernametype, username) valueS(3, 'Last Name', 'LN3')
insert into #temp1(userid, usernametype, username) valueS(4, 'First Name', 'FN4')
insert into #temp1(userid, usernametype, username) valueS(4, 'Last Name', 'LN4')

enter image description here

SQl Query

SELECT  userid, [First Name], [Last Name]
FROM    
( 
    SELECT userid, usernametype, username FROM #temp1
) 
p 
PIVOT 
( 
    Max(username) FOR usernametype 
    IN ([First Name], [Last Name])
) AS pvt

Final Result set

enter image description here

Upvotes: 1

MatBailie
MatBailie

Reputation: 86775

SELECT
  [fn].UserID,
  [fn].UserName,
  [ln].UserName
FROM
  yourTable AS [fn]
INNER JOIN
  yourTable AS [ln]
    ON [fn].UserID = [ln].UserID
WHERE
      [fn].UserNameType = 'FirstName'
  AND [ln].UserNameType = 'LastName'

Upvotes: 2

josh.trow
josh.trow

Reputation: 4901

To avoid subqueries and the speed penalties that can arise form them, just join yourself.

SELECT DISTINCT
 T1.UserID UserID, 
 T1.UserName FirstName, 
 T2.UserName LastName
FROM 
 Users T1 JOIN Users T2 
  ON T1.UserID = T2.userID
WHERE 
 T1.UserNameType = 'FirstName'
 AND T2.UserNameType = 'LastName'

EDIT: Should be fixed now.

Upvotes: 1

mellamokb
mellamokb

Reputation: 56779

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

Upvotes: 3

Lourens
Lourens

Reputation: 1518

You could use a subquery

SELECT 
    UserID,
    UserName as FirstName,
    (SELECT top 1 UserName 
     FROM myTable 
     WHERE UserID = t.UserID 
     and UserNameType = 'Last Name') as LastName
FROM myTable t
WHERE t.UserNameType = 'First Name'

Upvotes: 1

Related Questions