Riddhi Valecha

Riddhi Valecha

  • 632
  • 3.2k
  • 333.2k

Dynamic Columns in SQL Query

Dec 10 2013 5:01 AM
Hi all...

I need help in creating dynamic columns in SQL Query.

My requirement is -
The user have to select 2 dates from the front-end.
Let the selected dates be - Jan-2010 to Dec-2013.

The table in the database that I have is-
ID  Company Location DateCount Status 
 1TCS  Mumbai  1-1-2010 1Clear 
 2 Zensar Hydrabad 2-2-2010 2 Rejected
 3 Infosys Pune 3-3-2011 4 Clear
 4 IBM Pune 4-4-2011 3 Clear
 5 Microsoft Singapore 5-5-2012 5 Rejected
 6 Google US 6-6-2013 Rejected

Total Months (Jan-2010 to Dec-2013 =48 Months) Columns.

Total Status = 2 (Clear and Rejected).

Hence, total dynamic columns = 48 * 2 = 96

Name of Columns - Jan2010Clear, Jan2010Rejected, Feb2010Clear, Feb2010Rejected, etc and so on.

The output I want is is follows -

 Company LocationJan2010Clear Jan2010Rejected Feb2010Clear Feb2010Rejected Mar2011Clear March2011Rejected  Total


 1 0 0 2 4 0 1+2+4=7




























 Total 0 0 2 1+2+4=7

I am unable to get the result using pivot query.

Please help me out...

Thanks a ton to all.. in advance...

Answers (2)