July 10, 2006 at 12:08 pm
An example to illustrate my problem.
I want to group records based on Warehouse. There are 4 (NY, TX, WA, NV)
SQL Statement returns 8 records
SELECT * FROM Items
SKU | Status | Date | Warehouse
111111 | Sale | 1/1/2006 | NY
111111 | Sale | 1/1/2006 | TX
111111 | Sale | 1/1/2006 | WA
111111 | Sale | 1/1/2006 | NV
222222 | Reg | 2/2/2006 | NY
222222 | Reg | 2/2/2006 | TX
333333 | Sale | 3/3/2006 | NY
333333 | Sale | 4/1/2006 | NY
Instead I would rather have this returned:
111111 | Sale | 1/1/2006 | ALL
222222 | Reg | 2/2/2006 | NY, TX
333333 | Sale | 3/3/2006 | NY
333333 | Sale | 4/1/2006 | NY
Thanks in advance for any replies.
July 10, 2006 at 12:43 pm
-- prepare test data
declare @test-2 table (SKU int, Status varchar(4), dt datetime, Warehouse varchar(2))
insert @test-2
select 111111, 'Sale', '1/1/2006', 'NY' union all
select 111111, 'Sale', '1/1/2006', 'TX' union all
select 111111, 'Sale', '1/1/2006', 'WA' union all
select 111111, 'Sale', '1/1/2006', 'NV' union all
select 222222, 'Reg', '2/2/2006', 'NY' union all
select 222222, 'Reg', '2/2/2006', 'TX' union all
select 333333, 'Sale', '3/3/2006', 'NY' union all
select 333333, 'Sale', '4/1/2006', 'NY'
-- Do the work!
declare @output table (id int identity(0,1), SKU int, Status varchar(4), dt datetime, Warehouses varchar(10))
declare @id int,
@items int,
@SKU int,
@status varchar(4),
@dt datetime,
@Warehouses varchar(10)
insert @output (SKU, Status, dt)
select distinct sku,
status,
dt
from @test-2 -- Change to Items table
order by sku,
status,
dt
select @id = max(id)
from @output
while @id >= 0
begin
select @sku = sku,
@status = status,
@dt = dt,
@warehouses = '',
@items = 0
from @output
where id = @id
select @warehouses = case when @warehouses <> '' then @warehouses + ', ' else '' end + warehouse,
@items = @items + 1
from (
select distinct top 100 percent warehouse
from @test-2 -- Change to Items table
where sku = @sku
and status = @status
and dt = @dt
order by warehouse
) z
update @output
set warehouses = case when @items = 4 then 'ALL' else @warehouses end
where id = @id
select @id = @id - 1
end
select sku,
status,
dt,
warehouses
from @output
N 56°04'39.16"
E 12°55'05.25"
July 11, 2006 at 7:38 am
Or you could utilize a function
CREATE FUNCTION dbo.udf_warehouse (@SKU int, @status varchar(4), @Date datetime)
RETURNS varchar(100)
AS
BEGIN
DECLARE @result varchar(100)
SELECT @result = COALESCE(@result+',','') + Warehouse
FROM Items
WHERE SKU = @SKU
AND Status = @status
AND [Date] = @Date
ORDER BY Warehouse
RETURN @result
END
SELECT SKU, Status, [Date],
CASE WHEN COUNT(*)=4 THEN 'ALL' ELSE dbo.udf_warehouse(SKU, Status, [Date]) END
FROM Items
GROUP BY SKU, Status, [Date]
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply