Creating View with columns values taken from other tables

• sg2000-429020

  Ten Centuries

  Points: 1106

  More actions

  February 11, 2008 at 8:50 am

  #203464

  My DB contains 3 tables:

  TabA that contains a column called ColA with values 'a1', 'a2'

  TabB that contains a column called ColB with value 'b1'

  TabC that contains a column called ColC with values 'c1', 'c2', 'c3'

  I want to create a View with two columns:

  View_Col1 contains the name of the table

  View_Col2 contains the value of the specific column (i.e. ColA for TabA, ColB for TabB, and ColC for TabC) in the table.

  For example: the View for the above example will have the following records:

  View Record 1: View_Col1: 'TabA'; View_Col2: 'a1'

  View Record 2: View_Col1: 'TabA'; View_Col2: 'a2'

  View Record 3: View_Col1: 'TabB'; View_Col2: 'b1'

  View Record 4: View_Col1: 'TabC'; View_Col2: 'c1'

  View Record 5: View_Col1: 'TabC'; View_Col2: 'c2'

  View Record 6: View_Col1: 'TabC'; View_Col2: 'c3'

  Any ideas how can I accomplish this objective? Thanks very much for any comments.

  sg2000

• Grant Fritchey

  SSC Guru

  Points: 398876

  More actions

  February 11, 2008 at 9:32 am

  #776952

  Since you're not, in any way relating the data between these tables, an odd thing, you could do something like this:

  SELECT 'TableA' AS TableName

  ,Cola AS FirstColumn

  FROM dbo.TableA

  UNION ALL

  SELECT 'TableB' AS TableName

  ,Colb AS FirstColumn

  UNION ALL

  SELECT 'TableC' AS TableName

  ,Colc AS FirstColumn

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning