Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71

    Unanswered: Convert rows to colums

    I am getting this from my client
    TableA
    StorID LineNumber Text
    30000 1 ClientName
    30000 2 ClientSurname
    30000 3 3333333
    30000 4 20-05-2004

    I actually want this to look like
    TableB
    StoryID Name Surname Policy Date
    30000 Name Surname 3333333 20-05-2004

    Can you help with the script to convert this to one record with many fields (as in TableB) if this is

  2. #2
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    you need a sort of a pivot table where the cross function is the identity function. I've used this code in a project, and it works fine,..though performance are surely not the best. Otherwise you can wait for SQL2005 :-)

    some very useful code (very general, you probably should modify it to best suit your db schema) can be found here:

    http://www.sqlteam.com/item.asp?ItemID=2955
    Davide Mauri
    http://www.davidemauri.it

  3. #3
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    keep it simple....

    create a function (called get_val) with storid, and linenumber as arguements.

    in the function:

    select <text> from tableA
    where storid = <arg1> and linenumber = <arg2>
    return <text>


    then...to populate table b:

    INSERT INTO tableB (storid,name,surname,policy_date)
    SELECT DISTINCT storid, get_val(storid,1),get_val(storid,2),get_val(storid ,3),get_val(storid,4) FROM tableA

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest using something like:
    Code:
    SELECT a.storID
    ,  Min(CASE WHEN 1 = a.LineNumber THEN a.[Text]) AS [Name]
    ,  Min(CASE WHEN 2 = a.LineNumber THEN a.[Text]) AS [Surname]
    ,  Min(CASE WHEN 3 = a.LineNumber THEN a.[Text]) AS [Policy]
    ,  Min(CASE WHEN 4 = a.LineNumber THEN a.[Text]) AS [Date]
       FROM TableA AS a
       GROUP BY a.StorID
    -PatP

  5. #5
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    yup..

    much better.....nice pat.

Posting Permissions

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