Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  Transact-SQL (2008)  transpose data where values match across rows in d

Author  Topic  simoncs Starting Member 1 Post Posted - 2010-10-25 : 19:28:53 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