Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2010
    Posts
    2

    Unanswered: tranpose data from one table

    Hi
    hope the subject is correct.
    essentially i have one table where values in one column and row match the values in another column and row. This process repeats a few times to eventually get to the final reference.
    so i'd like to transpose this information so each value goes into a separate column and creates one row of data.
    here's an example of the table

    M_LABEL M_REF M_FATHER_L M_FATHER_R M_D_DATA0 M_D_DATA2
    GLOBAL NODE 0 0 1 0
    SCS GROUP 1 GLOBAL NODE 0 2 0
    ACCT1 1,240 SCS GROUP 1 3 0
    ACCT1 GLOB 1,246 ACCT1 1,240 4 0
    ACCT1 IPG 1,247 ACCT1 GLOB 1,246 5 0
    ACCT 01 1,248 ACCT1 IPG 1,247 6 0
    IPG ACCT1 1,249 ACCT 01 1,248 7 24,314
    ALLIANCE H 2 SCS GROUP 1 3 0
    ALLIANCE H GLOB 12 ALLIANCE H 2 4 0
    ALLIANCE H FPG 41 ALLIANCE H GLOB 12 5 0
    ALL 26001000 46 ALLIANCE H FPG 41 6 0
    ALLIANCE AU 47 ALL 26001000 46 7 25
    SCS BKG GRP 3 SCS GROUP 1 3 0
    SCSBG FIJI 944 SCS BKG GRP 3 4 0
    SCSBG HK 572 SCS BKG GRP 3 4 0
    SCSBG HK FXO 1,476 SCSBG HK 572 5 0
    HKfxotemp 1,477 SCSBG HK FXO 1,476 6 0
    HK FXO BTB ON 2,046 HKfxotemp 1,477 7 25,027
    HK FXO RBS ON 2,002 HKfxotemp 1,477 7 24,958
    HK FXO VAN ON 1,478 HKfxotemp 1,477 7 24,501
    and to produce a table like this:

    COL1 COL2 COL3 COL4 COL5 COL6 COL7 REFERENCE
    GLOBAL NODE SCS GROUP ACCT1 ACCT1 GLOB ACCT1 IPG ACCT 01 IPG ACCT1 24,314
    GLOBAL NODE SCS GROUP ALLIANCE H ALLIANCE H GLOB ALLIANCE H FPG ALL 26001000 ALLIANCE AU 25
    GLOBAL NODE SCS GROUP SCSBG FIJI SCSBG HK SCSBG HK FXO HKfxotemp HK FXO BTB ON 25,027
    GLOBAL NODE SCS GROUP SCSBG FIJI SCSBG HK SCSBG HK FXO HKfxotemp HK FXO RBS ON 24,958
    GLOBAL NODE SCS GROUP SCSBG FIJI SCSBG HK SCSBG HK FXO HKfxotemp HK FXO VAN ON 24,501
    where col1 will always be Global node or m_d_data0 = 1
    col2 is m_label where m_d_data0 = 2
    col3 is m_label where m_d_data0 = 3
    etc
    so m_d_data0 is essentially the group or level and dependant on this level the m_ref will match with m_father _l on the row above.
    so m_d_data0 = 7 is the final level and then on this row you get the final reference field being m_d_data2
    eg
    where m_d_data0 = 7 then (m_father_l = m_ref where m_d_data0 = 6). this is repeated all the way back up.
    i have tried doing this with case statements and unions. however i am thinking there are several ways of doing it, and that in itself seems to be confusing me.
    thanks for your help
    Simon
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2010
    Posts
    1

    Lightbulb Solution

    Hi,

    It's a tricky job. Below is a way to solve this problem. I assume you don't know the max level. So i use dynamic queries.
    But if the max level is fixed, you don't need dynamic queries and you may easily reduce following code.

    Stephane




    Code:
    -- **************************************************************************
    -- Create static temporary table dynamically. Numbers of columns depending on
    --   # of levels in the tree.
    -- **************************************************************************
    declare @i int,
            @sCreate varchar(1024) 			-- will contain "create table" command
    		
    select @i =max(M_D_DATA0) from Table1  	-- max level define # of columns
    while (@i > 0)  						-- add column to the create query, one by one
      begin
        select @sCreate = "COL" + convert(varchar,@i) 
                          + " varchar(32) null,"
                          + @sCreate
    	 where @i > 1
    	select @i = @i -1
      end
    select @sCreate = "create table tempdb..TransposeTable (COL1 varchar(32)," + @sCreate + " REFERENCE int)"
    execute (@sCreate) 						-- create table 
    go
    
    
    -- **************************************************************************
    -- insert leafs into temporary table and then shift data as long as there
    --   is a parent node.
    -- **************************************************************************
    declare @i int,
    		@sUpdate varchar(1024) 			-- will contain update query string
    select @i =max(M_D_DATA0) from Table1 	-- max level define # of columns
    while (@i > 0)							-- add column to the create query, one by one
      begin
        select @sUpdate = ", COL" + convert(varchar,@i)  
    	                  + " = COL" + convert(varchar,@i - 1) 
    					  + @sUpdate
    	 where @i > 1
    	select @i = @i -1
      end
    select @sUpdate = "update tempdb..TransposeTable set COL1 = s.M_FATHER_L" 
                      + @sUpdate 
    				  + " from Table1 s where s.M_LABEL = tempdb..TransposeTable.COL1 and s.M_D_DATA0 > 1"
    -- insert leafs into temporary table
    insert into tempdb..TransposeTable (COL1, REFERENCE)
         select M_LABEL, M_D_DATA2
    	   from Table1 s
    	  where M_D_DATA2 > 0
    -- shift data as long as there is a parent node
    while (exists(select 1 
                    from tempdb..TransposeTable t, Table1 s
    			   where t.COL1 = s.M_LABEL
    			     and s.M_D_DATA0 > 1))
      begin
        execute(@sUpdate)
      end
    -- return data
    select * from tempdb..TransposeTable
    -- drop static temporary table
    drop table tempdb..TransposeTable 
    go

  3. #3
    Join Date
    Oct 2010
    Posts
    2
    thanks for the reply.

    i managed to do it with a series of insert statements into a temp table.

Posting Permissions

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