manipulation issue - 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)

manipulation issue

Author

Topic

nbs
Starting Member

22 Posts

Posted - 2007-07-17 : 16:35:51

Hello
I have a temp table. The table consists of
cl num, cont date, code, daycount

eg: 51c23| 11/21/2006| p1| 4
51c23| 11/22/2006| p2| 4
51c23| 11/21/2006| p3| 3
51c24| 10/1/2006| p1| 1
51c12| 1/1/2006| p2| 0
51c12| 1/2/2006| p3| 2

I want to manipulate it as

cl num p1 date p1count p2date p2count p3 date p3count
51c23| 11/21/2006| 4| 11/22/2006| 4| 11/21/2006| 3
51c24| 10/1/2006| 1|
51c12| 1/1/2006| 0| 1/2/2006| 2

any ideas??
thanks

cas_o
Posting Yak Master

154 Posts

Posted - 2007-07-17 : 18:11:14

If this is a typo:
51c12| 1/1/2006| p2| 0
51c12| 1/2/2006| p3| 2
i.e. it should be:
51c12| 1/1/2006| p1| 0
51c12| 1/2/2006| p2| 2

then this works:


select
	t1.[cl num],
	t1.[cont date] p1date,
	t1.daycount p1count,
	t2.[cont date] p2date,
	t2.daycount p2count,
	t3.[cont date] p3date,
	t3.daycount p3count
	
from
	#table t1 
	left join #table t2 on t1.[cl num] = t2.[cl num] and t2.code = 'p2'
	left join #table t3 on t1.[cl num] = t3.[cl num] and t3.code = 'p3'
where 
	t1.code = 'p1'

Is the set {'p1','p2','p3' ...} finite ?

If you search here or in your favorite search engine for 'pivot SQL Server' or 'Cross Tab Sql Server' you should get plenty of links that go into more depth.

;-]... Quack Waddle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources