Solved

T-SQL Matrix build

Posted on 1998-09-17
2
660 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 200 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Copy a TadoDataSet to another and apply changes to database. 11 1,290
SyBase SQL Syntax 7 322
Using Select Top (Select From ) 5 490
Sybase - Backup and restore db into other server 1 584
Enabling the Skype for Business Meeting Scheduler in Hybrid OWA
Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

685 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