Please help - setting order of select in a self-referencing table

Hello,
I have a table of product categories that looks like this (air code, so
could be some typos, but it's basically right)...
create table producttypes (
ptype int not null identity(1,1) primary key,
typename varchar(50) not null default '',
ParentCat int references producttypes(ptype)
)
This allows categories to contain subcategories and so on.
In order to allow me to see the hierarchy, I am using the following
code, modified from some found in "Inside SQL Server 7.0" by Kalen
Delaney.
-- SQL starts
declare @.level int, @.current int
create table #stack (depthlevel int, ptype int)
create table #orgchart (seqno int identity, orglevel int not null, ptype int
not null)
set rowcount 1
select @.level=1, @.current=ptype from producttypes where ptype=parentcat
set rowcount 0
insert into #stack (depthlevel, ptype) values (@.level, @.current)
while (@.level>0)
begin
if exists (select * from #stack where depthlevel=@.level)
begin
set rowcount 1
select @.current=ptype from #stack where depthlevel=@.level
set rowcount 0
insert into #orgchart (orglevel, ptype) select @.level, @.current
delete from #stack where depthlevel=@.level and ptype=@.current
insert into #stack select @.level+1, ptype from producttypes where parentcat=
@.current and parentcat<>ptype
if @.@.ROWCOUNT > 0 select @.level=@.level+1
end
else
select @.level=@.level-1
end
select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
join producttypes as e on e.ptype=o.ptype order by o.seqno
drop table #stack, #orgchart
-- SQL ends
This produces a set of results that gives me all the categories. I can
use the orglevel field to tell what level I'm at, so if it changes, I
can see if I've gone up or down the hierarchy.
Now, the problem is that I can't work out how to set the order of
categories. For example, if the above SQL produces...
1 16 16 Products
2 17 16 Outdoor Toys
3 1 17 Trampolines
3 2 17 Slides
3 3 17 Swings
3 4 17 Accessories
3 5 17 Climbing Frames
3 7 17 Bicycles
you can see that the categories on level 3 are not in any obvious order.
I would like to have them in alphabetical order.
Any ideas how I would modify the above SQL to do this? TIA
Alan Silver
(anything added below this line is nothing to do with me)Try
select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
join producttypes as e on e.ptype=o.ptype order by o.seqno, e.typename
The results should be sorted first by the orglevel, and results within the
same orglevel will be sorted alphabetically. The syntax reads like "order
results first by o.seqno, then by e.typename"
"Alan Silver" wrote:

> Hello,
> I have a table of product categories that looks like this (air code, so
> could be some typos, but it's basically right)...
> create table producttypes (
> ptype int not null identity(1,1) primary key,
> typename varchar(50) not null default '',
> ParentCat int references producttypes(ptype)
> )
> This allows categories to contain subcategories and so on.
> In order to allow me to see the hierarchy, I am using the following
> code, modified from some found in "Inside SQL Server 7.0" by Kalen
> Delaney.
> -- SQL starts
> declare @.level int, @.current int
> create table #stack (depthlevel int, ptype int)
> create table #orgchart (seqno int identity, orglevel int not null, ptype i
nt not null)
> set rowcount 1
> select @.level=1, @.current=ptype from producttypes where ptype=parentcat
> set rowcount 0
> insert into #stack (depthlevel, ptype) values (@.level, @.current)
> while (@.level>0)
> begin
> if exists (select * from #stack where depthlevel=@.level)
> begin
> set rowcount 1
> select @.current=ptype from #stack where depthlevel=@.level
> set rowcount 0
> insert into #orgchart (orglevel, ptype) select @.level, @.current
> delete from #stack where depthlevel=@.level and ptype=@.current
> insert into #stack select @.level+1, ptype from producttypes where par
entcat=@.current and parentcat<>ptype
> if @.@.ROWCOUNT > 0 select @.level=@.level+1
> end
> else
> select @.level=@.level-1
> end
> select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
> join producttypes as e on e.ptype=o.ptype order by o.seqno
> drop table #stack, #orgchart
> -- SQL ends
>
> This produces a set of results that gives me all the categories. I can
> use the orglevel field to tell what level I'm at, so if it changes, I
> can see if I've gone up or down the hierarchy.
> Now, the problem is that I can't work out how to set the order of
> categories. For example, if the above SQL produces...
> 1 16 16 Products
> 2 17 16 Outdoor Toys
> 3 1 17 Trampolines
> 3 2 17 Slides
> 3 3 17 Swings
> 3 4 17 Accessories
> 3 5 17 Climbing Frames
> 3 7 17 Bicycles
> you can see that the categories on level 3 are not in any obvious order.
> I would like to have them in alphabetical order.
> Any ideas how I would modify the above SQL to do this? TIA
> --
> Alan Silver
> (anything added below this line is nothing to do with me)
>|||>Try
>select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
>join producttypes as e on e.ptype=o.ptype order by o.seqno, e.typename
>The results should be sorted first by the orglevel, and results within the
>same orglevel will be sorted alphabetically. The syntax reads like "order
>results first by o.seqno, then by e.typename"
Mark,
Thanks for the reply, but if you look carefully, it's not that simple.
Your suggestion will list all level 2 categories together, followed by
all level 3 and so on. This will not give the correct hierarchy as there
may be several level 2 categories each with subcategories (ie level 3).
If this isn't clear, look at the longer category listing shown later on.
This is a more complete list than the abbreviated one I showed before.
Note that the ordering is done on seqno, which is the order in which the
categories were put into the orgchart table. The ordering is not done on
the level at all.
I think I need to modify the way the categories are pulled out of the
producttypes table when they are inserted into the stack table. Trouble
is, I can't see how to control the ordering as they are being pulled one
at a time.
Thanks for the reply. Any further help would be appreciated.
Longer category listing follows...
level typename
1 Products
2 Outdoor Toys
3 Trampolines
3 Slides
3 Swings
3 Accessories
3 Climbing Frames
3 Bicycles
2 Indoor Toys
3 Snooker Tables
3 Boy's Toys
4 Hard Puzzles
3 Girl's Toys
4 Easy Puzzles
3 Board Games
3 Pets
4 Ferrets
4 Dogs
4 Cats
>"Alan Silver" wrote:
>
Alan Silver
(anything added below this line is nothing to do with me)sql

Pivotting Data

I have this table:

CREATE TABLE [dbo].[SHIP_HISTORY] (
[SHIPID] [int] IDENTITY (1, 1) NOT NULL ,
[PRODUCT] [nvarchar] (18) NOT NULL ,
[ORDERNUM] [char] (10) NOT NULL ,
[SHIP_DATE] [smalldatetime] NOT NULL ,
[WHSE] [nvarchar] (5) NOT NULL ,
[UNITS] [real] NOT NULL
) ON [PRIMARY]

It contains 4 years of sales history.

I need to pivot the data for a form in my front end (Access). The query was simple in Access, but too much data caused the form to be too slow.
I'm thinking I'll pivot the data in SQL first, and just link my front end to the new table.

I started to write a sproc to do this, and tested it before getting too far. For some reason, this is returning 166 rows, when I expect to seee only 4 (one for each year).
There is one row for each year that contains my totaled data, and the rest of the rows contain zeros. Any ideas what would be causing this?

Here's the SQL I'm using:

SELECT YEAR(sHIP_DATE) AS YEAR,
'1' = CASE WHEN DatePart(ww,[SHIP_DATE]) = 1 THEN SUM(UNITS)
ELSE 0
END,
'2' = CASE WHEN DatePart(ww,[SHIP_DATE]) = 2 THEN SUM(UNITS)
ELSE 0
END
FROM SHIP_HISTORY
GROUP BY Year([SHIP_DATE]), DatePart(ww,[SHIP_DATE])

If anyone has a better idea for how to do this, I'd welcome that, too!

ThanksYou are grouping by DatePart(ww,[SHIP_DATE]), and thus get rows for each of these values, even though you are not displaying DatePart(ww,[SHIP_DATE]) in your SELECT list.|||Try this:

SELECT YEAR(sHIP_DATE) AS YEAR,
SUM(CASE WHEN DatePart(ww,[SHIP_DATE]) = 1 THEN UNITS ELSE 0 END) AS '1',
SUM(CASE WHEN DatePart(ww,[SHIP_DATE]) = 2 THEN UNITS ELSE 0 END) AS '2'
FROM SHIP_HISTORY
GROUP BY Year([SHIP_DATE])|||[edit] posted before reading your second post|||Try this:

SELECT YEAR(sHIP_DATE) AS YEAR,
SUM(CASE WHEN DatePart(ww,[SHIP_DATE]) = 1 THEN UNITS ELSE 0 END) AS '1',
SUM(CASE WHEN DatePart(ww,[SHIP_DATE]) = 2 THEN UNITS ELSE 0 END) AS '2'
FROM SHIP_HISTORY
GROUP BY Year([SHIP_DATE])

That works perfectly.
Thanks, Blindman!

Pivoting a results table MSSQL

I have a query in which I would like to pivot the results

I presently have my results displaying something like this.

OrderNumber Product OrderQuantity
----- ----- -------
0608 Prod1 3
0608 Prod2 12
0608 Prod3 2

What I am after is for the results to display something like this.

OrderNumber Prod1 Prod2 Prod3
----- --- --- ---
0608 3 12 2

This is using SQL Server ver 8.0

SELECT OrderNumber,SUM(CASE WHEN Product='Prod1' THEN OrderQuantity ELSE 0 END) AS Prod1,SUM(CASE WHEN Product='Prod2' THEN OrderQuantity ELSE 0 END) AS Prod2,SUM(CASE WHEN Product='Prod3' THEN OrderQuantity ELSE 0 END) AS Prod3

FROM MyTable

GROUP BY OrderNumber

|||

Thank you, that worked perfectly!