Cross Tab or Pivot - Need Help

• VisualCPP-328615

  Grasshopper

  Points: 21

  More actions

  August 12, 2013 at 8:06 am

  #278587

  I have the following tables.

  TabParent

  ========

  id item1 item2

  -- ------ ------

  AA 2 2

  TabChild

  ======

  id f1 f2 f3 f4 f5

  -- -- -- -- -- --

  AA 1 2 3 4 1

  AA 1 1 1 2 2

  AA 1 1 1 3 3

  Now what I want is (i.e each row from child becomes appended as column sets in single row)

  -----------------------------------------------------------------------------------------

  id item1 item2 f11 f12 f13 f14 f15 f21 f22 f23 f24 f25 f31 f32 f33 f34 f35

  -- ------ ------ --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

  AA 2 2 1 2 3 4 1 1 1 2 2 2 1 1 1 3 3

  Please let me know,

  1. Is it possible to do?

  2. If yes how a static as well as dynamic (if no of rows in child table for a key increases) solutions could be implemented through T-SQL code?

  Thanks and Advance

• Sean Lange

  SSC Guru

  Points: 286609

  More actions

  August 12, 2013 at 8:09 am

  #1640610

  Check out the articles referenced in my signature. They explain in detail how to do both a static and dynamic version of cross tabs.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/