Displaying values in columns instead of rows

Question

Post reply

Displaying values in columns instead of rows

Sridhar-137443

Hall of Fame

Points: 3762
More actions
April 11, 2006 at 8:48 am

#112983

Hi,
I need to write a stored procedure that accepts startdate, enddate. I need to display the data between these dates in the following format
Code, Amount_year1, Hours_Year1, Quantity_Year1, Amount_year2, Hours_year2, Quantity_year2 .....
where year1, year2, .. are the years that are in between startdate and enddate. How could I do that?
Thanks,
Sridhar.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Can you please post a sample table structure, with some sample data, and what your expected results are.

http://www.aspfaq.com/etiquette.asp?id=5006

Sergiy SSC Guru Points: 110208 More actions · Answer 2

There must be dozen of topics with the same question (and answers!) for the last couple of weeks!

_____________
Code for TallyGenerator

David le Quesne SSCertifiable Points: 5229 More actions · Answer 3

Hi,

suggest you use a searched CASE statement

See BOL or

http://msdn2.microsoft.com/en-us/library/ms181765.aspx

Each column can be a separate case statement

SELECT

CASE WHEN <condition1> THEN <value> ELSE NULL END,

CASE WHEN <condition2> THEN <value> ELSE NULL END

... etc

David

If it ain't broke, don't fix it...

Beratung SSC Eights! Points: 940 More actions · Answer 4

What would be the disadvantage of using derived tables like:

Select

IM.Item, ISYR1.SalesYR1, ISYR2.SALESYR2

from ItemMaster IM

Inner Join

(Select Item, ItemSales as SalesYR1 from SalesDetail where Date <= @param1 and Date >= @param2) ISYR1

on IM.ITEM = ISYR1.ITEM

Inner Join

(Select Item, ItemSales as SalesYR1 from SalesDetail where Date <= @param3 and Date >= @param4) ISYR1

on IM.ITEM = ISYR2.ITEM

steve dassin SSCrazy Points: 2396 More actions · Answer 5

The RAC utility will easily generate all kinds of static and dynamic crosstabs.Unless of course you want to try to reinvent the wheel or fumble thru a lot of tortuned code

http://www.rac4sql.net

Mike C SSC-Insane Points: 23224 More actions · Answer 6

Sounds like what you want is what Access users refer to as a "Pivot table". Here's some FREE solutions to achieve the desired result:

http://www.sqlteam.com/item.asp?ItemID=2955

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=680&lngWId=5

The second example is simpler to use, but is designe to query on only one table at a time. There may be even more solutions available if you search SSC for pivot tables.