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 2008 Forums
 Transact-SQL (2008)
 Change result set layout

Author  Topic 

Darts75
Starting Member

27 Posts

Posted - 2014-05-13 : 02:53:12
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)

17689 Posts

Posted - 2014-05-13 : 05:44:46


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
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

Darts75
Starting Member

27 Posts

Posted - 2014-05-14 : 02:02:11
Thank you KH
Go to Top of Page
   

- Advertisement -