Results 1 to 3 of 3

Thread: tsql - convert a vertical resulset to a horizontal resultset

  1. 05-22-2007, 01:47 PM #1
    ecka00
    ecka00 is offline Registered User
    Join Date
    May 2005
    Posts
    111

    tsql - convert a vertical resulset to a horizontal resultset

    here is my query:
    select distinct SEC_RIGHT_ID from SEC_USER_RIGHT where USERID = 10

    here is the resultset:
    SEC_RIGHT_ID
    ------------
    ABT_INQ
    ABTENT_ADD
    ABTENT_DEL
    etc......

    i need the resultset to be
    10 ABT_INQ ABTENT_ADD ABTENT_DEL etc....
    Reply With Quote Reply With Quote
  2. 05-23-2007, 08:13 AM #2
    skhanal
    skhanal is offline Experts
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    SQL Server provides a new statement called PIVOT for such transformation.

    For earlier version you will have to write query with UNION, but if you do not know how many columns will be needed then it gets more complex as you need to generate the query dynamically.
    Reply With Quote Reply With Quote
  3. 05-23-2007, 08:38 AM #3
    rmiao
    rmiao is offline Registered User
    Join Date
    Sep 2002
    Posts
    5,938
    Yes if you use sql2k5. For sql2k, check 'cross-tab report' in books online.
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules