Tricky select. Any help? - SQL Server Forums

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

Old Forums

CLOSED - General SQL Server

Tricky select. Any help?

Author

Topic

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2005-12-02 : 11:25:23

I have a table:


tbComponents
ID | Mark
------------------
1  | 401

tbComponentStrands
ID | Qty | Material
-------------------
1  | 4   | 1/2"
1  | 2   | 5-7/16"

I need to create a view where it would result in:
vComponentStrands
ID | Pattern
------------------------
1  | (4)1/2" + (2)5-7/16"

Any ideas on how I can do this?

Mike B

jhermiz

3564 Posts

Posted - 2005-12-02 : 12:14:45

SELECT ID, MIN(Qty) + ' ' + MIN(MATERIAL) AS B, MAX(Qty) + ' ' + MAX(Material) AS C FROM TABLE2
GROUP BY ID

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

jhermiz

3564 Posts

Posted - 2005-12-02 : 13:08:20

Actually that probably wont work, it depends on your data, is it always a max of 2 per ID ?

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2005-12-02 : 13:38:43

quote:
Originally posted by jhermiz

Actually that probably wont work, it depends on your data, is it always a max of 2 per ID ?

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

Well, I tried a couple of things and the only thing I can get to work is by using a cursor within a UDF. The only problem is that I cannot use the ORDER BY when declaring the cursor. Is this normal. I figured I can use any SELECT statement with a cursor.


DECLARE Pattern CURSOR FOR (SELECT     TOP 100 PERCENT t1.Quantity,
                                      t2.Descr, t1.Location
			    FROM dbo.tbHollowcoreComponentStrands t1
                            INNER JOIN dbo.tbMaterials t2 
                            ON t1.fkMaterialID = t2.MaterialID
                            WHERE     
                            (t1.fkProjectNumber = @cProject) AND
                            (t1.fkComponentID = @cComponent)
                            ORDER BY t1.Location ASC)

Any thoughts?

Mike B

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-03 : 02:00:32

Try to simulate the method described here
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-03 : 07:49:40

use the trick in this function of avoid the cursor:

 --run this whole block to see it work
set nocount on
create table tbComponents (ID int, Mark int)
create table tbComponentStrands (ID int,Qty int, Material varchar(10))
go
insert tbComponents (id,mark) 
select 1 , 401 union all
select 2, 402

insert tbComponentStrands
select 1,  4,   '1/2"' union all
select 1,  2,   '5-7/16"' union all
select 2, 100, '2-1/4"' union all
select 2, 10, '1/4"'
go

create function dbo.udf_componentPattern(@componentid int)
returns varchar(200)
as
begin
	declare @pattern varchar(200)
	
	select @pattern = coalesce(@pattern + ' + ' + '(' + convert(varchar,qty) + ')' + Material, 
				'(' + convert(varchar,qty) + ')' + Material)
	from tbComponentStrands
	where id = @componentid

	return @pattern
end
go

select id,  dbo.udf_componentPattern(id) as pattern
from	tbcomponents

go
drop function dbo.udf_componentPattern
drop table tbComponentStrands
drop table tbComponents

btw, if you remove the enclosing paranthesis from the cursor definition, I think your error goes away. I think removing the "top 100 percent" will also do the trick.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-03 : 08:01:08

Also, for you and anyone else, as long as you're going to the trouble of typing the structure and contents of your tables like this:

quote:
tbComponents
ID | Mark
------------------
1 | 401

tbComponentStrands
ID | Qty | Material
-------------------
1 | 4 | 1/2"
1 | 2 | 5-7/16"

make it easy on yourself and us by scripting the tables and paste that in, then just add a few insert statements as sample data. You'll get more responses and we won't have to guess at datatypes and stuff. Thanks!

create table tbComponents (ID int, Mark int)
create table tbComponentStrands (ID int,Qty int, Material varchar(10))
go
insert tbComponents (id,mark) 
select 1 , 401 union all
select 2, 402

insert tbComponentStrands
select 1,  4,   '1/2"' union all
select 1,  2,   '5-7/16"' union all
select 2, 100, '2-1/4"' union all
select 2, 10, '1/4"'
go

Be One with the Optimizer
TG

jhermiz

3564 Posts

Posted - 2005-12-03 : 21:03:13

TG i love your posts, slick solution.

Good to have you on board.

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-04 : 15:10:14