Combining Records -Formatted Repost-

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 2000 Forums

SQL Server Development (2000)

Combining Records -Formatted Repost-

Author

Topic

cardgunner

326 Posts

Posted - 2007-04-27 : 14:28:06

This is going to be a good one. I’m not very good at SQL. What I know I
learned from a couple of books and from searching here or online. I
have not found anything on what I’m trying to do. In a table (dscrp) I
have 4 fields(unit, spec, desc, seq). There is no primary. The results
of the table look like this:

  
unit    spec                        desc                           seq    
------- --------------------------- ------------------------------ ------ 
26124   168                         HP                             1
26124   31590lbs.                   Oper. Weight                   2
26124   3.5                         Yd Class                       3
26124   yes                         Coupler                        4
26124   yes                         Ride Control                   5
26124   23.5x25 L-3 Michelin XHA    Tires                          6
26124   yes                         Aux Hyd                        7
26124   Counterweight,Beacon        Other Options                  8
70222   17,700lbs                   Operating Weight               1
70222   61hp                        Horse Power                    2
70222   18" Rubber                  Tracks                         3
70222   89" 2 way                   Dozer Blade                    4
70222   14'9"                       Dig Depth                      5
70277   185hp                       Horse Power                    1
70277   84"                         Drum Size                      2
70277   33810lbs                    Operating Weight               3

What I'm trying to achieve is


unit		description
--------------- ------------------------------------------------------------
26124           168hp, 31590lbs. Oper. Weight, 3.5Yd Class, Coupler, Ride 
                Control, 23.5x25 L-3 Michelin XHA Tires, Aux Hyd, Counter
                weight,Beacon
70222		17,700lbs. Operating Weight, 61hp Horse Power, 18" Rubber 
                Tracks, 89" 2 way Dozer Blade, 14'9" Dig Depth
70277		185hp Horse Power, 84" Drum Size, 33810lbs Operating Weight

some of the logic I'd like other then how to get all of this toogether is

CASE WHEN spec IN ("yes" or "no") THEN '' ELSE spec END
CASE WHEN desc="Other Options" THEN '' ELSE spec END

table dscrp will be joined to table main by unit
where as my final result would be, as an example:


unit   year make model description                               price
----   ---- ---- ----- ----------------------------------------- -----
26124  2005 IR   DD110 168hp, 31590lbs. Oper. Weight, 3.5Yd      $123,000
                       Class, Coupler, Ride Control, 23.5x25 L-3 
                       Michelin XHA Tires,Aux Hyd, Counterweight
                       ,Beacon

Any Help on combining spec and desc in order in a text string per each unit.

From Pressure Comes Diamonds
Card Gunner

Card Gunner

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-27 : 14:53:49

[code]
Declare @table table (unit int, spec varchar(100), descr varchar(100), seq tinyint)
insert into @table
select 26124, '168' ,'HP' , 1 union all
select 26124, '31590lbs.' ,'Oper. Weight' , 2 union all
select 26124, '3.5' ,'Yd Class' , 3 union all
select 26124, 'yes' ,'Coupler' , 4 union all
select 26124, 'yes' ,'Ride Control' , 5 union all
select 26124, '23.5x25 L-3 Michelin XHA' ,'Tires' , 6 union all
select 26124, 'yes' ,'Aux Hyd' , 7 union all
select 26124, 'Counterweight,Beacon' ,'Other Options' , 8 union all
select 70222, '17,700lbs' ,'Operating Weight' , 1 union all
select 70222, '61hp' ,'Horse Power' , 2 union all
select 70222, '18" Rubber' ,'Tracks' , 3 union all
select 70222 , '89" 2 way' ,'Dozer Blade' , 4 union all
select 70222, '14''9"' ,'Dig Depth' , 5 union all
select 70277, '185hp' ,'Horse Power' , 1 union all
select 70277, '84"' ,'Drum Size' , 2 union all
select 70277, '33810lbs' ,'Operating Weight' , 3

select * from @table

--create function dbo.gnGetValues() with following code
declare @unit int
set @unit = 26124
Declare @res varchar(1000)
select @res = ISNULL(@res,'')
+ ' '
+ CASE WHEN spec ='yes' or spec = 'no' THEN '' ELSE spec END
+ ' '
+ CASE WHEN descr='Other Options' THEN '' ELSE descr END
+ ','
from @table where unit = @unit

--call the function as

select unit, gnGetValues(unit)
from yourTable
[/code]

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

cardgunner

326 Posts

Posted - 2007-04-27 : 15:06:48

Thanks Dinakar,
I think...
but I've well over 2000 records to do.
So I'm not sure if I get it. I'm very new at all this, although I've worked with MS Access alot.
Can I get the code explained to me and maybe I could put in the field names.

Card Gunner

Card Gunner

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-27 : 18:30:59

create a function that takes in a unit and returns the entire concatenated string. just call the function from your SELECT as I showed. Now you have to realize this can be a slow query or can have some performance impact as using a function on a column in SELECT can cause table scans. If the data you are dealing with is few thousand rows, perhaps it should be okay but you are the better judge.
The code I provided has comments about what code to put in the function and how to call it.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-27 : 20:50:49

also refer to http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources