Cross tab like query

• hiYusuf

  SSC Enthusiast

  Points: 151

  More actions

  September 19, 2011 at 1:03 pm

  #248302

  Dear All,

  I am creating a report of State wise Article Price for all states of India.

  I have data in table in below format :

  Main Group | Article No | Color | Size Name | StateName | VATPercent | VATPrice | CSTPercent | CSTPrice

  Group 1 | Article 1 | RED | XL | DELHI | 4.12 | 1200 | 2.00 | 1150

  ....

  .....

  .......

  this will continue for 28 states for 1 Article 1 color and 1 size then another size

  Group 1 | Article 1 | RED | XXL | DELHI | 4.12 | 1200 | 2.00 | 1150

  ....

  .....

  .......

  this will continue for 28 states then

  Group 1 | Article 1 | BLACK | XL | DELHI | 4.12 | 1200 | 2.00 | 1150

  ....

  .....

  .......

  NOW I WANT TO THIS REPORT IN BELOW FORMAT :

  Main Group | Article No | Color | Size Name | DELHI_VATPercent | DELHI_VATPrice | DELHI_CSTPercent | DELHI_CSTPrice .......

  in this case total column will be 4 column + (28 state x 4)column = 116

  Thanks & Regards,

  Yusuf

• drew.allen

  SSC Guru

  Points: 77013

  More actions

  September 20, 2011 at 11:12 am

  #1384026

  hiyusuf (9/19/2011)

  ---

  Dear All,

  I am creating a report of State wise Article Price for all states of India.

  You should be doing this in SSRS (or whatever reporting software you are using) instead of T-SQL. SSRS is much better equipped to create crosstabs.

  If you insist on using T-SQL, then search this site for articles on "crosstabs" or "dynamic crosstabs".

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA