Results 1 to 2 of 2
  1. #1
    Join Date
    May 2011
    Posts
    1

    Unanswered: Do i need to use pivot?

    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:
    Code:
    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:
    Code:
    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Several ways to skin this cat.

    If you know for a fact that you will have a maximum of four, this will work:
    Code:
    declare	@MyData table
    	(No_ int,
    	ReceiptDate date,
    	Quantity int)
    	
    insert into @MyData
    select 1100548, '2011-08-09', 900
    union select 1100807,'2011-07-04', 1150 
    union select 1100807, '2011-07-04', 50
    union select 1100814, '2011-07-04', 1100
    union select 1100814, '2011-09-09', 2000
    union select 1100814, '2011-07-03', 1100
    union select 1100821, '2011-08-09', 1440
    union select 1100814, '2011-07-03', 244
    
    ;with OrderedData as
    	(select	No_,
    		convert(varchar(10), Quantity) + ' (' + CONVERT(varchar(10), ReceiptDate) + ')' as DisplayText,
    		ROW_NUMBER () over (Partition by No_ order by ReceiptDate) as ColumnNum
    	from	@MyData)
    select	One.No_,
    	One.DisplayText as '1st',
    	Two.DisplayText as '2nd',
    	Three.DisplayText as '3rd',
    	Four.DisplayText as '4th'
    from	OrderedData as One
    	left outer join OrderedData as Two
    		on One.No_ = Two.No_
    		and Two.ColumnNum = 2
    	left outer join OrderedData as Three
    		on One.No_ = Three.No_
    		and Three.ColumnNum = 3
    	left outer join OrderedData as Four
    		on One.No_ = Four.No_
    		and Four.ColumnNum = 4
    where One.ColumnNum = 1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •