Create table columns from table rows

• tnpich

  Ten Centuries

  Points: 1223

  More actions

  October 17, 2011 at 2:30 pm

  #249432

  Not sure how to create this report most efficiently in SQL Server 2008 R2.

  I have a table Initiatives:

  Init_ID INT PK

  Init_Name Varchar(25)

  The number may change at any time

  I have another table InitDependencies

  DI_ID INT PK

  Init_ID INT FK to Initiatives.Init_ID

  DM_ID INT FK to DeviceMaster.DM_ID

  A device ID may be related to many Initiatives but Init_ID & DM_ID is a unique combination in InitDependencies

  I need to be able to create a table/report that would show

  Init_Name_1 Init_Name_2 ..... Init_Name_x

  DM_ID_1 X X

  DM_ID_2 X

  So I'm looking at creating a table/view where the rows of Initiatives become columns and rows of InitDependencies.

  This can be done as a triggered stored procedure.

  What's the best way of accomplishing this?

  Thanks

• SGT_squeequal

  SSCertifiable

  Points: 7167

  More actions

  October 17, 2011 at 2:35 pm

  #1395587

  im not sure i entirly get what your asking for but, as i read it sounds like you want to pivot the data, look up pivot tables

  ***The first step is always the hardest *******

• drew.allen

  SSC Guru

  Points: 77009

  More actions

  October 17, 2011 at 2:39 pm

  #1395590

  The most efficient way to do this is to use the correct tool for the job. That tool is a reporting tool (such as SSRS). T-SQL is most definitely NOT the correct tool for the job even though it is possible to do what you ask in T-SQL.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• tnpich

  Ten Centuries

  Points: 1223

  More actions

  October 17, 2011 at 3:21 pm

  #1395604

  Hopefully this explains it more:

  Initiative Table

  Init_IDInit_Name

  1InitiativeA

  2InitiativeB

  3InitiativeC

  InitDependency Table

  DI_IDInit_IDDM_ID

  11P10

  22P10

  31Q01

  43R22

  52R22

  Table/Report output

  DeviceInitiativeAInitiativeBInitiativeC

  P10Yes Yes

  Q01Yes

  R22 Yes Yes

  Seems I need to use a dynamic sql pivot

• tnpich

  Ten Centuries

  Points: 1223

  More actions

  October 17, 2011 at 3:22 pm

  #1395605

  Thanks Drew. I'll have to try that as well