Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: creating pivot table

    Hallo,

    I have a table with 3 columns:
    table tbl_user_class
    ===============
    user_id (int) PK
    class_id (varchar(20)) PK
    class_value(varchar(100))

    values are like so:

    user_id class_id class_value
    ======================
    1 firstname Rogier
    1 lastname Doekes
    2 firstname Mary
    3 lastname Smith
    .....

    I would like to create result set in the following format

    user_id firstname lastname
    ====================
    1 Rogier Doekes
    2 Mary Smith
    ......


    How do I accomplish this? I tried using CASE WHEN statements but the best I could come up with was this:
    1 Rogier null
    1 null Doekes
    2 Mary null
    2 null Smith

    when I did the following t-SQL statement:
    select userID,
    CASE WHEN classID = 'firstname' THEN classvalue END as 'firstname',
    CASE WHEN classID = 'lastname' THEN classvalue END as 'lastname'
    FROM tbl_user_class

    Thanks for any help,

    -Rogier Doekes

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99([user_id] int, class_id varchar(15), class_value varchar(15))
    GO
    
    INSERT INTO myTable99([user_id], class_id, class_value)
    SELECT 1, 'firstname', 'Rogier' UNION ALL
    SELECT 1, 'lastname',  'Doekes' UNION ALL
    SELECT 2, 'firstname', 'Mary'   UNION ALL
    SELECT 2, 'lastname',  'Smith'  UNION ALL
    SELECT 3, 'firstname', 'Brett'  UNION ALL
    SELECT 4, 'lastname',  'Kaiser'
    GO
    
       SELECT a.[user_id], b.FirstName, c.LastName
         FROM (SELECT Distinct [User_id] FROM myTable99) AS a
    LEFT JOIN (SELECT [user_id], class_value as FirstName FROM myTable99 WHERE class_id = 'firstname') AS b
           ON a.[user_id] = b.[user_id]
    LEFT JOIN (SELECT [user_id], class_value as LastName FROM myTable99 WHERE class_id = 'lastname') AS c
           ON a.[user_id] = c.[user_id]
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2004
    Posts
    2
    that does the job,

    Thanks a lot Brett

    -Rogier

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •