Do i need to use pivot here? - 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 2005 Forums

Transact-SQL (2005)

Do i need to use pivot here?

Author

Topic

Freek
Starting Member

2 Posts

Posted - 2011-05-20 : 05:14:44

Hi Guys,

As a newby i already tried searching the forum for an answer first before posting. Unfortunately all cases that I looked at where a bit different from my problem (i guess

)

My source table:


No_	Receipt Date	Quantity
1100548	2011-08-09 	900
1100807	2011-07-04 	1150 
1100807	2011-07-04 	50
1100814	2011-07-04 	1100
1100814	2011-09-09 	2000
1100814	2011-07-03 	1100
1100821	2011-08-09 	1440

I would like to have this result:


No_     1st                    2nd                   3th                  4th
1100548 900 (2011-08-09)       -                     -                    -
1100807 1150 (2011-07-04)      50 (2011-07-04)       -                    -
1100814 1100 (2011-07-04)      2000 (2011-09-09)     1100 (2011-07-03)    -
1100821 1440 (2011-08-09)      -                     -                    -

This data represents purchase lines. We have at the moment about 2000 lines in this table. A lot of those records have different receipt dates but i know for sure that we never have more then 4 purchase lines for 1 product at a time. So this means i don't wan't to display more then 4 columns.
When i have 1 purchase line for a product, i wan't to fill in the first column, when i have 2 line i wan't to fill in the first 2 columns,...

I've tried experimenting with PIVOT which looked like the obvious solution, but i don't want to use an aggregate function (there is no need to make a sum or do a count).
I don't want to use my dates as column headers, because this would mean that i might end up with 60 or more columns.

What kind of query can i write to have the desired result?

Thanks for looking at my problem.

regards.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-20 : 05:51:08

something like

with cte as
(
select *, val = convert(varchar(20),Quantity) + ' (' + convert(varchar(12),[Receipt Date],112) + ')', seq = row_number() over (partition by No_ order by Date)
from tbl
)
select No_
, [1st] = = max(case when seq = 1 then val end)
, [2st] = = max(case when seq = 2 then val end)
, [3st] = = max(case when seq = 3 then val end)
, [4st] = = max(case when seq = 4 then val end)
from cte
group by No_

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

Freek
Starting Member

2 Posts

Posted - 2011-05-20 : 06:06:09

Thanks Nigel!
This looks like a solution. I'm going to experiment on the actual table and translate your solution to the correct fields!

I'll post back asap.

reg.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources