Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2007
    Location
    India
    Posts
    21

    Unanswered: Query to Convert Variable no of rows to columns

    I am having data in a table like this:

    ID Name Value Posn
    HOXE EMAIL g.mang@tjh.com 30
    HOXE EMAIL g1.mang1@tjh.com 31
    HOXE FAX 91-821-2660585 20
    HOXE PHONE 91-821-2660420 10

    KTSA EMAIL ga.jais@tjh.com 30
    KTSA FAX 91-2990-150444 20
    KTSA PHONE 91-2990-151874 10
    KTSA PHONE 91-2990-152638 11


    LTSA EMAIL la.lais@tjh.com 30
    LTSA FAX 91-2997-550444 20
    LTSA FAX 91-2997-550444 21
    LTSA PHONE 91-2997-551874 10



    I want output as 4 columns like this:
    ID Phone Fax Email
    HOXE 91-821-2660420 91-821-2660585 g.mang@tjh.com, g1.mang1@tjh.com
    KTSA 91-2990-151874, 91-2990-152638 91-2990-150444 ga.jais@tjh.com
    LTSA 91-2997-551874 91-2997-550444, 91-2997-550444 la.lais@tjh.com

    Can you Please Help?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Code:
    use tempdb
    go
    create table dbo.t (ID char(4) not null, [Name] varchar(5) not null, [Value] varchar(25) not null, Posn int not null)
    go
    insert dbo.t
    select 'HOXE','EMAIL','g.mang@tjh.com',30 union all
    select 'HOXE','EMAIL','g1.mang1@tjh.com',31 union all
    select 'HOXE','FAX','91-821-2660585',20 union all
    select 'HOXE','PHONE','91-821-2660420',10 union all
    select 'KTSA','EMAIL','ga.jais@tjh.com',30 union all
    select 'KTSA','FAX','91-2990-150444',20 union all
    select 'KTSA','PHONE','91-2990-151874',10 union all
    select 'KTSA','PHONE','91-2990-152638',11 union all
    select 'LTSA','EMAIL','la.lais@tjh.com',30 union all
    select 'LTSA','FAX','91-2997-550444',20 union all
    select 'LTSA','FAX','91-2997-550444',21 union all
    select 'LTSA','PHONE','91-2997-551874',10 
    go
    create function dbo.fn_Value (@ID char(4), @Name varchar(5)) returns varchar(1024)
    as
       begin
       declare @retVal varchar(1024)
       set @retVal = ''
       select @retVal = @retVal + case when @retVal = '' then [value] else ',' + [Value] end
          from dbo.t where ID = @ID and [Name] = @Name
       return @retVal
       end
    go
    select distinct
       ID
      ,PHONE = dbo.fn_Value(ID, 'PHONE')
      ,FAX   = dbo.fn_Value(ID, 'FAX')
      ,EMAIL = dbo.fn_Value(ID, 'EMAIL')
       from dbo.t 
    go
    drop function dbo.fn_Value
    drop table dbo.t
    go
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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