I need some help as i dont know where to start...
I have a table with 3 columns. Car ID, Car Make, Car price
I am needing an alphabet, to be returned, with car make that can be a reference point for its ID and Price
Select car_make from table_cars where car_make like 'A%'
Results:
Alpha Romeo
Audi
Acura
For every car found, a dynamic alphabet reference is assigned. Alpha Romeo is returned as Alpha Romeo (A), Audi is returned is Audi (B) and Acura is returned as Acura (C)
Now for every car found, i would like to be able to get the price and reference it by the alphabet used for that car. Such that the results will be
$250,000 (A)
$25,500 (B)
$18,000 (C)
what if you get 27 rows in you result set? what alphabet would it be?
CREATE TABLE #Brands
(
BrandName varchar(30)
, CarPrice money
)
INSERT INTO #Brands
VALUES('Alpha Romeo', 250000.00),('Audi', 25500.00),('Acura', 18000.00)
,('Beetle', 100.00)
SELECT [ReferenceLetter] = CHAR(64+ROW_NUMBER()
OVER
(
PARTITION BY LEFT(BrandName, 1)
ORDER BY BrandName
))
, BrandName
, CarPrice
FROM #Brands
GO
DROP TABLE #Brands
GO
Next time please provide Create Table and Sample data (INSERT statements) to save us having to do that, as well as expected output. Otherwise someone, and often several people, wind up doing that and ... guess what ... we often guess that wrongly and find we have wasted our time.
2 Likes
THe max # of cars in table are less than 20. So that shouldn't be a problem. P.S there are few inner and outer joins in there too
Agreed. I should've done that.
Thank you so much. It worked