Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Unanswered: selecting rows as columns

    I would like to select rows as columns in select statement from a table
    like:
    custid phone
    1 111
    1 222
    1 333
    row data to be selected into one line 1 111 222 333

    I have to join this data to the other tables.

    Would some one help a select statement for this purpose?

    Thanks

  2. #2
    Join Date
    May 2002
    Posts
    299

    Re: selecting rows as columns

    For a known number of columns...

    select custid,
    max(case when phone=111 then phone end) '111',
    max(case when phone=222 then phone end) '222',
    max(case when phone=333 then phone end) '333'
    from
    (select 1 custid,111 phone
    union all select 1,222
    union all select 1,333
    union all select 2,111)x
    group by custid

    Btw, RAC could easily do this + more...
    --
    -oj
    http://www.rac4sql.net

  3. #3
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Re: selecting rows as columns

    Originally posted by vkaramched
    I would like to select rows as columns in select statement from a table
    like:
    custid phone
    1 111
    1 222
    1 333
    row data to be selected into one line 1 111 222 333

    I have to join this data to the other tables.

    Would some one help a select statement for this purpose?

    Thanks
    Your suggestion is great and appreciated. The problem is I do not know the number of records for custid and I also do not know the values of custid and phone upfront. The custid is just one example with 1 but many custids with varying number of phones can exist. In that case the solution may not be that easy select statement. Would some one think of a better way?

    Thanks

  4. #4
    Join Date
    May 2002
    Posts
    299

  5. #5
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80

    Re: selecting rows as columns

    I had the same problem, some month ago. The problem is that SQL Server accepts only his built in aggregate functions: SUM,MIN,MAX,COUNT,... If it has an aggregate like CONCATENATE then we haven't had any problem at all.In my opinion the only way you can solve this is by selecting data:



    select custid, phone order by custi.



    Then make a report to view this recordset. Put in it a group on custi, and in the footer of that group display information:

    cusid, variable1


    where variable1 is a var that has an initial value of "", resets with every group, and it calculates variable1=variable1+phone (this is the solution for Visual Fox Pro report)



    In Access you should enter a running sum textbox in detail band of group custid. with source property =[phone] and running sum property=over group. Then, make the detail band unvisible. In the footer band of custid group insrt a textbox that actually displays the value of the previous mentioned textbox (this new textbox is not an running sum one)


    Good Look!


    IONUT



    PS. I'm a big big fan of MS SQLServer 2k, but in this filed, of aggregation function, PostgreSQL is the best one. It permit developers to implement their own aggregate functions.

    The same problem rises if you want to obtain the product of some values based on a common value of another field that you group by.

    MICROSOFT please allow us to build our own aggregate functions with future versions of SQL Server 2k!!!!!!!!!!!!!!!!!!!!

Posting Permissions

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