Wierd SQL2000 query sorted numerically 2

[Webkins]

Here is my table with column names and data. Column part is datatype varchar, part1-part11 are datatypes real:

master part1 part2 part3 part4 part5 part6 part7 part8 part9 part10 part11
2222 132 154 264 308 0 350 0 0 0 0 351
4747 128 134 190 254 260 285 0 0 0 0 286

Here is the resulting 2 column dataset that I want to achieve. I have no clue how to accomplish this or if it is even possible.
Sorted numerically with master number for reference:

master data
2222 0
2222 0
4747 0
2222 0
4747 0
2222 0
4747 0
2222 0
4747 0
4747 128
2222 132
4747 134
2222 154
4747 190
4747 254
4747 260
2222 264
4747 285
4747 286
2222 308
2222 350
2222 351

Thank you very much for any and all suggestions and help. I am lost here

 

[SkipVought]

...and you SQL?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Your SQL

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Webkins]

At this time I have nothing other than this idea. I just started working on this today and I am searching here and Google for possible answers.

 

[Webkins]

Here is what I have so far that pulls the 2 records from the table which contains thousands of records:

select master, part1, part2, part3, part4, part5, part6, part7, part8, part9, part10, part11
from coilspec.dbo.coilspec
where coil = 2222

union select select master, part1, part2, part3, part4, part5, part6, part7, part8, part9, part10, part11
from coilspec.dbo.coilspec
where coil = 4747

Now I need to sort and or transform the data to the format above.

 

[fredericofonseca]

you need to look at how UNPIVOT works - then order by the value, the pivotcolumn name and the master column name.

The above should be enough for you to do it, or to at least try creating the remaining sql you need and come back with more issues.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[gmmastros]

Frederico,

The title says SQL2000, so pivot won't work.

Webkins, you show an example query that returns 12 columns unioned with another query that returns the same columns. All you really need to do is to take this one step further, like this.

select master, part1 As [Data]
from coilspec.dbo.coilspec
where coil = 2222

Union All

select master, part2
from coilspec.dbo.coilspec
where coil = 2222

Union All

select master, part3
from coilspec.dbo.coilspec
where coil = 2222

Union All

select master, part4
from coilspec.dbo.coilspec
where coil = 2222

Order By Data

Your example is with a union query. My query above, uses union all. The difference between union and union all is that union will filter out distict data where union all does not. Since union is doing more work than union all, it's a little slower (or a lot slower if you have a lot of data).

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Webkins]

OMG, That worked, THANK YOU SO VERY MUCH !

 

[fredericofonseca]

George - yes you are right - my mistake

Webkins - another solution that uses a equivalent of unpivot and I think the subselect will work on SQL 2000

the unions alls of 1, 2,3 ... can be replaced with a select from a number tables if you got one - for lots of values it would be preferable and will for sure work with sql2000 if the subselect doesn't.

The code below does give you exactly the output you asked for - the union alls from George may or not depending on some factors e.g. it will give you the same number of rows and values per row, but not necessarily the on same order

select master
      ,data
from (
select master
      ,occur_no
      ,case
       when occur_no = 1 then part1
       when occur_no = 2 then part2
       when occur_no = 3 then part3
       when occur_no = 4 then part4
       when occur_no = 5 then part5
       when occur_no = 6 then part6
       when occur_no = 7 then part7
       when occur_no = 8 then part8
       when occur_no = 9 then part9
       when occur_no = 10 then part10
       when occur_no = 11 then part11
       end as data
from x

cross join
(
select 1 as occur_no union all 
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11 
) t
) t1
order by data
        ,occur_no
        ,master

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886