From Rows to column - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

From Rows to column

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-02-06 : 20:28:26

Good day i have this data...

Select dateTrans, ItemCode, QTY from tblALC

dateTrans------ItemCode-----QTY
02/01/2005 ... 11111111-----1
02/01/2005 ... 11111111-----2
02/01/2005 ... 11111112-----1
02/02/2005 ... 11111112-----1
02/02/2005 ... 11111111-----2
02/03/2005 ... 11111112-----5
02/03/2005 ... 11111112-----1
02/03/2005 ... 11111111-----2
02/04/2005 ... 11111112-----2
02/04/2005 ... 11111112-----1
02/04/2005 ... 11111111-----3
02/04/2005 ... 11111111-----1
02/04/2005 ... 11111111-----1
02/05/2005 ... 11111111-----10
02/05/2005 ... 11111112-----5
02/06/2005 ... 11111111-----5
02/06/2005 ... 11111112-----5

I want to show with this output...

ItemCode---02/01/2005--02/02/2005--02/03/2005--02/04/2005--02/05/2005--02/06/2005
_________________________________________________________________________________
11111111--- 3 2 2 5 10 5
11111112--- 1 1 6 3 5 5

I want to sum up the qty per dates in a column.
HOW?

Want Philippines to become 1st World COuntry? Go for World War 3...

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-06 : 20:33:32

select
[20050201] = (select sum(qty) from tbl t2 where dateTrans = '20050201' and t2.ItemCode = t.ItemCode) ,
[20050202] = (select sum(qty) from tbl t2 where dateTrans = '20050202' and t2.ItemCode = t.ItemCode) ,
[20050203] = (select sum(qty) from tbl t2 where dateTrans = '20050203' and t2.ItemCode = t.ItemCode) ,
[20050204] = (select sum(qty) from tbl t2 where dateTrans = '20050204' and t2.ItemCode = t.ItemCode) ,
[20050205] = (select sum(qty) from tbl t2 where dateTrans = '20050205' and t2.ItemCode = t.ItemCode) ,
[20050206] = (select sum(qty) from tbl t2 where dateTrans = '20050206' and t2.ItemCode = t.ItemCode)
from tbl t
group by ItemCode
order by ItemCode

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-02-06 : 20:59:56

tnx nr supposed i will used that in SP with parameter of this...

exec sp_Show from, to

How?

Want Philippines to become 1st World COuntry? Go for World War 3...

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-06 : 23:06:22

Then as you don't have a fixed number of columns or column names then you will need to use dynamic sql.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources