Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

T-SQL Matrix build

Posted on 1998-09-17
2
651 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Some of the SEO trends we might expect in 2017.
ConnectWise and their customers need to ensure critical alerts automatically reach the right person at the right time. MSP superheros efficiently respond to these alerts key is providing automatic, intelligent alerting that generates a complete audi…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

809 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