Creating View with columns values taken from other tables

• sg2000-429020

  Ten Centuries

  Points: 1046

  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: 397721

  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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software