Help regarding query

• M A A Mehedi Hasan-383686

  Grasshopper

  Points: 11

  More actions

  April 3, 2007 at 6:19 am

  #370116

  I have 2 tables like table-1 and table-2. I want to represent data like table-3. Is there any query which can retrive data from SQL Server2000 in my desired  format?

  ID Name Address 1 Mehedi Dhaka 2 Shaer Dhaka 3 Shuvo Dhaka

  Table -1

  ID fk_ID Products Quantity 1 1 Wrox 10 2 1 Orelly 20 3 1 Osborne 5 4 2 Wrox 20 5 2 MS Press 15 6 3 Osborne 60

  Table-2

  ID Name Address Wrox Orelly Osborne MS Press 1 Mehedi Dhaka 10 20 5 2 Shaer Dhaka 20 15 3 Shuvo Dhaka 60

  Table-3, Desired Report

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736129

  More actions

  April 3, 2007 at 8:22 am

  #698291

  First, please don't use so much whitespace in posts.

  second, what you want is a cross tab. If this is for a report, it's much better to do this in the client/reporting engine.

  IF not, you need to know how many columns you need. If you do, then

  select a.id, a.name. a.address. b.quantity 'wrox', c.quantity 'oreilly'

  from Table1 a

  left outer join table2 b

  on a.id = b.id

  and b.products = 'wrox'

  left outer join table2 c

  on a.id = b.id

  and b.products = 'oreilly'

• M A A Mehedi Hasan-383686

  Grasshopper

  Points: 11

  More actions

  April 3, 2007 at 11:04 pm

  #698461

  Dear Steve Jones,

  Thanks for your answer.