Select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select

Author

Topic

satheesh
Posting Yak Master

152 Posts

Posted - 2014-03-11 : 07:13:47

Hi All,

I have 'codes' and 'Description' field in 'ecode' table and 'codes' in quote table

values for 'Codes' in quote table is like

Quoteid Codes

0012 LB,WS
0031 WDC
--
--

In 'ecode' table 'description' column contain the description for each code

for eg :

Ecode Table : -

Codes Description
Lb - Late Booking
WS - Winter Sports
WDC - Wedding Cover
--
--

How to select 'description' when retrieving data from quote table

ie. i need

Output

0012 Latebooking,Winter Sports
0031 Wedding Cover

Any help will be highly appreciated.

Thanks

SG

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-11 : 08:26:40

If you have a choice, don't store the data this way as comma-separated strings. This is not a normalized table and so very difficult to work with. If this is all you have, one way to accomplish what you want to do would be use a string splitter function. Copy and install the string splitter function from this page. http://www.sqlservercentral.com/articles/Tally+Table/72993/ Then use it like in the example below:

CREATE TABLE #Codes(QuoteId VARCHAR(4),Codes VARCHAR(32));
INSERT INTO #Codes VALUES ('0012','LB,WS'),('0031','WDC');

CREATE TABLE #ECode (Codes VARCHAR(4), [Description] VARCHAR(32));
INSERT INTO #ECode VALUES ('Lb','Late booking'),
('WS','Winter sports'),('WDC','Wedding Cover');


;WITH cte AS 
(
	SELECT
		c.QuoteId,e.*,s.ItemNumber
	FROM
		#Codes c
		CROSS APPLY dbo.DelimitedSplit8k(Codes,',') s
		INNER JOIN #ECode e ON e.codes = s.Item
)
SELECT
	a.QuoteId,
	STUFF(b.[Descriptions],1,1,'') AS [Descriptions]
FROM
	(SELECT DISTINCT QuoteId FROM cte) a
	CROSS APPLY 
	( 
		SELECT ',' + [Description] FROM cte b
		WHERE a.QuoteId = b.QuoteId
		ORDER BY b.ItemNumber
		FOR XML PATH('')
	)b([descriptions]);

DROP TABLE #Codes;
DROP TABLE #ECode;

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-03-11 : 09:09:44

DECLARE @Code TABLE(Quoteid INT,Codes VARCHAR(20))
INSERT INTO @Code VALUES(0012,'LB,WS'),(0031,'WDC')

DECLARE @Des TABLE(QuoteId INT,Codes VARCHAR(10),Description VARCHAR(30))
INSERT INTO @Des VALUES(0012,'Lb','Late Booking'),(0012,'WS','Winter Sports'),(0031,'WDC','Wedding Cover')

;WITH Temp
AS
(
SELECT u.quoteid,u.codes,p.Description
FROM @code u
INNER JOIN @des p
ON ',' + u.Codes + ',' LIKE '%,' + CAST(p.Codes AS varchar(15)) + '%'
)
SELECT DISTINCT quoteid,
STUFF((SELECT ',' + Description FROM Temp WHERE Quoteid = t.QuoteId ORDER BY Quoteid FOR XML PATH('')),1,1,'') AS PublicationNames
FROM (SELECT DISTINCT Quoteid,Description FROM Temp) t

Veera

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources