Solved

T-SQL Matrix build

Posted on 1998-09-17
2
621 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
Comment Utility
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
Comment Utility
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
truncation error - math operation 6 557
SyBase SQL Syntax 5 617
SQL Query Syntax 5 166
Call sybase stored procedure from php 8 41
This article will show you how to create an ISO CD-ROM/DVD-ROM image (*.iso), and MD5 checksum signature, for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5). It's a good idea to compare checksums, because many installations fail because of a corr…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now