Can I convert a numeric string into referenced values with an SQL query?

Not all the column names where provided but try

SELECT        dbo.tblCarOrders.orderNumber, dbo.tblCarTypes.TEXT
FROM            dbo.tblCarTypes INNER JOIN
                         dbo.colCarTypes ON dbo.tblCarTypes.carID = dbo.colCarTypes.carID INNER JOIN
                         dbo.tblCarOrders ON dbo.colCarTypes.orderNumber = dbo.tblCarOrders.orderNumber

Select allOpen in new window

I guess I need to outline all table and column names for this to make sense, below is an example:

tblCarOrders:

ordernumber       |       colCarTypes
-----------------------------------------------------
ord134                   |      5,7

tblCarTypes:
cartype                   | carTypeID
---------------------------------------------
chevy                       |  5
dodge                      |  7

so i want to write a query to return:  ord134, chevy, dodge (where chevy, dodge is returned in 1 column)

What I have

SELECT tblCarOrders.orderNumber, tblCarTypes.cartype
FROM tblCarTypes
JOIN tblCarOrders ON tblCarOrders.colCarTypes LIKE CONCAT('%', tblCarTypes.carTypeID, '%')
GROUP BY tblCarOrders.orderNumber, tblCarTypes.cartype;

Select allOpen in new window

i would like to return only 1 record from the query, is that possible? In other words, in the cartype column from your example, there should be a comma separated list of the 2 car types, in one ONE record returned. Thank you.

Your table design doesn't follow the rules for a good database design and now you're struggling to write a query to provide what you want.
What you need is a splitter function. Good news is somebody already had the same issue and created a function that you can use. Here's one that you can use. You might want to perform your own search for other versions.

Hi,
Pls try this..

;WITH CTE AS
(
	SELECT o.orderNumber, t.cartype
	FROM tblCarTypes t
	INNER JOIN tblCarOrders o ON o.colCarTypes LIKE CONCAT('%', t.carTypeID, '%')
	GROUP BY o.orderNumber,t.cartype
)
SELECT a.orderNumber 
            , STUFF 
                ((
                SELECT ', ' + b.cartype
                FROM CTE b
                WHERE a.orderNumber = b.orderNumber
                FOR XML PATH('')
                ) ,1,2,'') 
                AS [cartype]
FROM CTE a
GROUP BY a.orderNumber

Select allOpen in new window

Hope it helps!

To combine the results of the query that I have given you to one record, you can use the following

SELECT ID,
STUFF((SELECT ',' + FieldName 
FROM Table
WHERE ID = t.ID
FOR XML PATH('')),1,1,'')
FROM (SELECT DISTINCT ID FROM Table)t

Select allOpen in new window

Code below uses the "standard" split function "dbo.DelimitedSplit8K".  If you don't already have that function, you can Google for the source, or just request it and I will post it.


CREATE TABLE #tblCarOrders (
    orderNumber varchar(30) NOT NULL,
    colCarTypes varchar(500) NULL
    )
INSERT INTO #tblCarOrders VALUES('ordernum779', '4,7')

CREATE TABLE #tblCarTypes (
    colCarType varchar(30) NOT NULL,
    colCarDescription varchar(200) NOT NULL
    )
INSERT INTO #tblCarTypes VALUES('4', 'Chevy'),('7', 'Toyota')


SELECT orderNumber,
    STUFF(CAST((
        SELECT ', ' + ct.colCarDescription
        FROM dbo.DelimitedSplit8K(co.colCarTypes, ',') ds
        INNER JOIN #tblCarTypes ct ON ct.colCarType = ds.Item
        FOR XML PATH('')
    ) AS varchar(8000)), 1, 2, '') AS carTypeDescriptions
FROM #tblCarOrders co

So every time that you'll need to split a string you'll write again all the code instead of using a defined function.
Great (not)!!!