Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

T-SQL Matrix build

Posted on 1998-09-17
2
Medium Priority
?
681 Views
Last Modified: 2012-08-13
I have a problem building , or reformatting, data held in a CAR information Database.

I want to display car model information for GB. This information display what features the model has.

FEATURE_CODE (for a car model) is held as many lines per  MODEL_NAME (the MODEL_NAME in unique). So when you display the models available in UNITED KINGDOM the MODEL_NAME is display as many times as there are FEATURE_CODE's

Current Recordset
-----------------

MODEL_NAME               GEOG_AREA FEATURE_CODE        
----------               --------- ------------        
FIAT CINQUENCENTO 1.1    GB        R                    
FIAT CINQUENCENTO 1.1    GB        RB                  
FIAT CINQUENCENTO 1.1    GB        S4                  
FIAT CINQUENCENTO 1.1    GB        3DR                  
FIAT CINQUENCENTO 1.1    GB        R/C                  
FIAT CINQUENCENTO 1.1    GB        SIBS                
VAUXHALL CORSA 1.2       GB        R                    
VAUXHALL CORSA 1.2       GB        RB                  
VAUXHALL CORSA 1.2       GB        S4                  
VAUXHALL CORSA 1.2       GB        3DR                  
VAUXHALL CORSA 1.2       GB        R/C                  
VAUXHALL CORSA 1.2       GB        SIBS                
VAUXHALL CORSA 1.2 MERIT GB        R                    
VAUXHALL CORSA 1.2 MERIT GB        RB                  
VAUXHALL CORSA 1.2 MERIT GB        S4                  
VAUXHALL CORSA 1.2 MERIT GB        3DR                  
VAUXHALL CORSA 1.2 MERIT GB        R/C                  
VAUXHALL CORSA 1.2 MERIT GB        SIBS                
NISSAN MICRA 1.0 LX      GB        RB                  
NISSAN MICRA 1.0 LX      GB        S4                  
NISSAN MICRA 1.0 LX      GB        3DR                  
NISSAN MICRA 1.0 LX      GB        ABG                  
NISSAN MICRA 1.0 LX      GB        R/C                  
NISSAN MICRA 1.0 LX      GB        SIBS                
VAUXHALL ASTRA 1.4 LSI   GB        RB                  
VAUXHALL ASTRA 1.4 LSI   GB        S5                  
VAUXHALL ASTRA 1.4 LSI   GB        5DR                  
VAUXHALL ASTRA 1.4 LSI   GB        ABG                  
VAUXHALL ASTRA 1.4 LSI   GB        R/C                  
VAUXHALL ASTRA 1.4 LSI   GB        SIBS                



I want to display 1 occurrence of MODEL_TYPE with the FEATURE_CODE's appended as one row e.g.

MODEL_NAME               GEOG_AREA FEATURE_CODE        
----------               --------- ------------        
FIAT CINQUENCENTO 1.1    GB        R,RB,S4,3DR,R/C,SIBS

0
Comment
Question by:ashleyavis
2 Comments
 
LVL 2

Accepted Solution

by:
Paullkha earned 600 total points
ID: 1098291
declare a cursor as follows:
/////////
declare cur_feature_codes as cursor
for select FEATURE_CODE
from CAR_TABLE
where MODEL_NAME = @model_argument
for read only
///////
declare two variables as follows:
//////
declare @codes as varchar(xxx)
declare @list_codes as varchar(xxx)
/////
fetch cur_feature_codes
into @codes
select @list_codes = @list_codes + "," + @codes
/////
@list_codes will be in the format you like; a string of feature codes!
Implement the above as a stored procedure that takes MODEL_NAME as an argument.

Any doubts or questions, please leave a comment.

Good Luck!
0
 

Author Comment

by:ashleyavis
ID: 1098292
That seems to work find, although a bit slow.

Do you know of any alternatives ? - MAX aggregation ?

If not , no worries : Thanks alot for you help.

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Transferring FSMO roles is done when an admin wants to split roles between certain Domain Controllers or the Domain Controller holding the Roles has been forcefully demoted using dcpromo / forceremoval
In this article, the configuration steps in Zabbix to monitor devices via SNMP will be discussed with some real examples on Cisco Router/Switch, Catalyst Switch, NAS Synology device.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question