Combining records - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Combining records

Author

Topic

cardgunner

326 Posts

Posted - 2007-04-27 : 12:35:05

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 fourfields(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

cardgunner

326 Posts

Posted - 2007-04-27 : 12:39:18

Shoot the tabs in this didn't work. I'll try reposting so it makes more sense

Card Gunner

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources