none
SQL query structuring question

    Question

  • Hi,

    I have a customer table as shown below: -

    Name           Work Number                 Home Phone               Mobile

    cust1            01274700300                 01274 600200            07898564320

    cust2            01274700301                                                  07898564321

    cust3                                                 01274 600202            07898564322

    I need to write a sql statement against this table to give me the output as: -

    cust1            Work Number                 01274700300

    cust1            Home Number                 01274 600200

    cust1            Mobile Number                 07898564320

    cust2            Work Number                 01274700301

    cust2            Mobile Number                 07898564321

    cust3            Home Number                 01274 600202

    cust3            Mobile Number                 07898564322

    Thanks.  Also if anyone knows how to do this in informix, that is even better.

    Friday, July 05, 2013 4:01 PM

Answers

  • I don't know for informix, but SQL Server has very cool UNPIVOT command that does exactly that:

    select u.Name, u.PhoneType, u.PhoneNumber
    from customer_table
    unpivot
    (    PhoneNumber for PhoneType in (work_number, home_number, mobile_number )
    ) u

    Result:

    Name    PhoneType    PhoneNumber
    cust1    work_number    01274700300
    cust1    home_number    01274 600200
    cust1    mobile_number     07898564320
    cust2    work_number    01274700301
    cust2    home_number    
    cust2    mobile_number     07898564321
    cust3    work_number    
    cust3    home_number    01274 600202
    cust3    mobile_number     07898564322


    Saturday, July 06, 2013 11:00 PM

All replies

  • I don't think there is a way to do exactly this with just T-SQL.  You can do this in SSRS, however.

    This should give you similar results, however. 

    create table customer_table
    (
    Name varchar(20),
    work_number varchar(12),
    home_number varchar(12),
    mobile_number varchar(12)
    )
    
    insert customer_table values('cust1','01274700300','01274 600200',' 07898564320')
    insert customer_table values('cust2','01274700301','',' 07898564321')
    insert customer_table values('cust3','','01274 600202',' 07898564322')
    
    drop table customer_table
    
    select 
    	Name,
    	case when work_number <> ''
    	then work_number end work_number1,
    	case when home_number <> ''
    	then home_number end home_number1,
    	case when mobile_number <> ''
    	then mobile_number end mobile_number1
    
    from customer_table
    


    Ryan D

    Saturday, July 06, 2013 3:03 PM
  • Using Ryan D's schema (except columns are NOT NULL) this would work:

    select Name, 'work' as 'number_type', work_number as 'number' 
      from customer_table where work_number <>''
    
    union all
    
    select Name, 'home' as 'number_type', home_number as 'number' 
      from customer_table where home_number <>''
    
    union all
    
    select Name, 'mobile' as 'number_type', mobile_number as 'number' 
      from customer_table where mobile_number <>''
    
    

    If you have the time, you might consider modifying the table structure to something like..

    CREATE TABLE PhoneNumber (
       CustomerId VARCHAR(20) NOT NULL,
       NumberType VARCHAR(10) NOT NULL,
       Number VARCHAR(20) NOT NULL
    )
    This structure would make it easier to add new number types, and might simplify querying.

    Saturday, July 06, 2013 4:56 PM
  • I don't know for informix, but SQL Server has very cool UNPIVOT command that does exactly that:

    select u.Name, u.PhoneType, u.PhoneNumber
    from customer_table
    unpivot
    (    PhoneNumber for PhoneType in (work_number, home_number, mobile_number )
    ) u

    Result:

    Name    PhoneType    PhoneNumber
    cust1    work_number    01274700300
    cust1    home_number    01274 600200
    cust1    mobile_number     07898564320
    cust2    work_number    01274700301
    cust2    home_number    
    cust2    mobile_number     07898564321
    cust3    work_number    
    cust3    home_number    01274 600202
    cust3    mobile_number     07898564322


    Saturday, July 06, 2013 11:00 PM