Display from vertical to horizontal

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Display from vertical to horizontal

Author

Topic

template
Starting Member

3 Posts

Posted - 2011-01-28 : 01:41:34

Hello Guys,

I'm new to sql programming. can you help me with this?

I have table like


Date     Quantity1  Quantity2   
1/1/11   1          4
1/2/11   2          5
1/3/11   3          6

and i want to display it like this in the report


Date            1/1/11      1/2/11     1/3/11 
Quantity1       1           2          3
Quantity2       4           5          6

*note the number of rows is dynamic
what query or technique can i use? thanks in advance !

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 01:45:10

Use PIVOT !

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-28 : 01:54:05

PIVOT + UNPIVOT


select	*
from	yourtable t
	unpivot
	(
		Qty for Type in (Quantity1, Quantity2)
	) p
	pivot
	(
		sum(Qty)
		for [Date] in ([2011-01-01], [2011-01-02], [2011-01-03])
	) p

KH
[spoiler]Time is always against us[/spoiler]

template
Starting Member

3 Posts

Posted - 2011-01-28 : 01:57:38

Thanks for the fast reply.

i'm trying to read about Pivot now.

@khtan the dates are dynamic and changes everytime

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-28 : 02:05:47

quote:
Originally posted by template

Thanks for the fast reply.

i'm trying to read about Pivot now.

@khtan the dates are dynamic and changes everytime

Then you will need to use Dynamic SQL to do it.

Since you are in reading mode, also read this http://www.sommarskog.se/dynamic_sql.html

KH
[spoiler]Time is always against us[/spoiler]

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 02:07:53

Yes, that will take some time (few hours) but hope it will resolve you issue :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources