Row value in column wise

• Inesh

  SSC Rookie

  Points: 29

  More actions

  September 11, 2007 at 11:12 pm

  #91347

  Hi,

  i have a question, where my 2 table contains data

  Table 1

  ColId     ColName

  1           Country

  2           Month

  3           Day

  Table 2

  tID        ColId      Txt

  1            1          US

  1            2          July

  1            3           4

  2            1          US

  2            2          Sep

  2            3           11

  3            1          US

  3            2          Dec

  3            3           25

  I need to query this 2 tables and get result like

  tId     Country      Month        Day

  1        US            July            4

  1        US            Sep           11

  1        US            Dec           25

  Can any one give the solution for this.

  Thanks

• Andras Belokosztolszki

  SSC-Insane

  Points: 22031

  More actions

  September 12, 2007 at 1:53 am

  #733032

  Hi Inesh,

  Pivot can do what you want to achieve. Since you do not know what columns you need to specify for the pivot operator, you need to construct the query dynamically based on the columns you have in the first table. I suggest you to read about PIVOT

  Meantime here is a solution:

  DECLARE @query NVARCHAR(4000)
  DECLARE @cols NVARCHAR(1000)
  SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                  '],[' + t2.ColName
                          FROM    Table1 AS t2
                          ORDER BY '],[' + t2.ColName
                        FOR
                          XML PATH('')
                        ), 1, 2, '') + ']' 
  
  SET @query = N'SELECT tID, '+
  @cols +'
  FROM 
  (SELECT  t2.tID
        , t1.ColName
        , t2.Txt
  FROM    Table1 AS t1
          JOIN Table2 AS t2 ON t1.ColId = t2.ColID) p
  PIVOT
  (
  MAX([Txt])
  FOR ColName IN
  ( '+
  @cols +' )
  ) AS pvt
  ORDER BY tID;'
  
  
  EXECUTE(@query)
  

  Regards,

  Andras

  ---

  Andras Belokosztolszki, MCPD, PhD
  GoldenGate Software