SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Change result set layout
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Darts75
Starting Member

Australia
19 Posts

Posted - 05/13/2014 :  02:53:12  Show Profile  Reply with Quote
Hi Everyone,

I am interested in changing the way that data is displayed in my result set.

Essentially I want to display a selection of rows (1 to n) as columns, the following diagram explains my intentions -



Perhaps one of the greatest challenges here is the fact that I do not have a concrete number of rows (or BIN numbers).

Each stock item could be stored in one or more BINS, which I will not know until running my query.

Any suggestions here will be greatly appreciated.

Kind Regards,

Davo

khtan
In (Som, Ni, Yak)

Singapore
17645 Posts

Posted - 05/13/2014 :  05:44:46  Show Profile  Reply with Quote


declare @max_bin_no int,
        @bin        int,
        @bins       varchar(max),
        @sql        varchar(max)

select  @max_bin_no = max(cnt)
from    (
            select  so_due_date, whs_code, qty_on_hand, cnt = count(*)
            from    table
            group by so_due_date, whs_code, qty_on_hand
        ) d

select  @bin = 1
while   @bin <= @max_bin_no
begin
        select @bins = isnull(@bins + ',', '') + quotename(convert(varchar(10), @bin))
        select @bin  = @bin + 1
end

select  @sql = 
'
    select *
    from   (
                select so_due_date, whs_code, qty_on_hand, bin_label,
                       bin_no = row_number() over ( partition by so_due_date, whs_code, qty_on_hand order by bin_label)
                from   table
           ) d
           pivot
           (
                max(bin_label)
                for  bin_no in (' + @bins + ')
           ) p
'
print @sql
exec (@sql)



KH
Time is always against us

Go to Top of Page

Darts75
Starting Member

Australia
19 Posts

Posted - 05/14/2014 :  02:02:11  Show Profile  Reply with Quote
Thank you KH
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000