Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2013

    Unanswered: SQL Query - Select Multiple rows in each single row with multiple columns

    I would like to select multiple rows in each row with multiple columns.

    Please find the attached doc as an example.

    Basically, I would like to combine multiple rows into a single row with multiple columns.
    In a real scenario there are huge no of records.

    Can anyone please suggest how to do this.


    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2011
    Pont l'Abbé, Brittany, France
    Provided Answers: 1
    Hi Joy,

    two ways to go there:
    A) an horrible and unmaintainable SQL Query: I resign from this one since I am not sure you can get what you want.

    B) The main issue is the variable length of the return record, this is why I would give preference to using a stored procedure using dynamic sql ( see Srinivasan's very good article at this place to understand how this works)

    High level design of the procedure
    1) dynamically create a temp table whose number of 'company name ' columns results from the max number of companies having the same policy, something like:
    create table rotated_output (
    policy_no integer,
    company_name_1 char(20),
    company_name_2 char(20),
    company_name_3 char(20),
    company_name_X char(20)
    First build the create table SQL statement within a string, then prepare from this string and execute the create table statement.

    2) declare a big cursor on policy details, client details, like this
    SELECT policy_no,company_name 
    FROM policy details p, client details c
    WHERE c.client_id = d.client_id
    ordered by policy_no,Company_name
    3) Scan this cursor:
    when the value of policy_id changes, insert a row into the temp table, primary key is policy_id
    on every row, update this row with the new company_name. you will need to use dynamic sql to build this query because the column name will change every time ( company_name 1, company_name 2 etc.....)
    4) once the temp table is completed, you have your data available in the way you want.

    Do you understand where I am pointing to?

    I am not aware of any existing SQL function that would do make a 90° rotation of the rows ( which is what Informix Warehouse Accelerator does ...)

    Calling for better solutions ?


Posting Permissions

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