May 27, 2008 at 1:15 pm
Ok, I'm sure this is really easy but I'm spinning my wheels.
I am exporting data from a table where there is one field that captures all detail information about a particular customer. This will be exported to Excel once I've figured out the code for the correct select statements. Here is the originating data set and results:
SELECT customer, details FROM account
RESULTS:
CUSTOMERDETAILS
Customer 1I
Customer 2E, I, A
Customer 3I, E, B, A
Customer 4I, B, E
In Excel; these details will equate to existing data:
I = 'Integrated Product'
E = 'Export'
A = 'Additional Module'
B = 'Booked Sale'
I have tried using variables and case statements for this but can't seem to get what I need. Ultimately, I would like the following results:
CUSTOMERDETAILS
Customer 1Integrated Product
Customer 2Export, Integrated Product, Additional Module
Customer 3Integrated Product, Export, Booked Sale, Additional Module
Customer 4Integrated Product, Booked Sale, Export
Could someone point me in the right direction? I was thinking that I could create variables for the new details:
@prod_i='Integrated Product'
@prod_e='Export'
@prod_b='Booked Sale'
etc.
and then string them together to give me the new details field layout:
(rtrim(@prod_i)+ ';' +rtrim(@prod_b) +';' +@prod_e +';') AS 'New Details'
BUT i'm stuck on how to set the variable based on a case or if/then. I'm trying to avoid creating a case statement for every possible iteration of the originating details field as it would end up being hundreds of possibilities. Any help would be great!
May 27, 2008 at 2:04 pm
This solution uses a numbers or tally table, see this article http://www.sqlservercentral.com/articles/TSQL/62867/ to split the items and an update from this article, http://www.sqlservercentral.com/articles/Advanced+Querying/61716/ within a scalar function to get the list. The scalar function creates some RBAR, so there may be a better way, but this should work (the smiley in the code is a closing parenthesis):
[font="Courier New"]ALTER FUNCTION dbo.fnConvertCodeToDescription
(
@codes VARCHAR(15)
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @codes_to_descriptions TABLE (row_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, single VARCHAR(25), list VARCHAR(100))
-- make all the members of the delimited string look the same
SET @codes = ',' + @codes + ','
-- table with each item
INSERT INTO @codes_to_descriptions
(
single
)
SELECT
CASE
WHEN LTRIM(SUBSTRING(@codes,N.N+1,CHARINDEX(',',@codes,N.N+1)-N.N-1)) = 'I' THEN 'Integrated Product'
WHEN LTRIM(SUBSTRING(@codes,N.N+1,CHARINDEX(',',@codes,N.N+1)-N.N-1)) = 'E' THEN 'Export'
WHEN LTRIM(SUBSTRING(@codes,N.N+1,CHARINDEX(',',@codes,N.N+1)-N.N-1)) = 'A' THEN 'Additional Module'
WHEN LTRIM(SUBSTRING(@codes,N.N+1,CHARINDEX(',',@codes,N.N+1)-N.N-1)) = 'B' THEN 'Booked Sale'
ELSE 'Unknown'
END AS single
FROM
dbo.Numbers N
WHERE
N.N < LEN(@codes) AND
SUBSTRING(@codes,N,1) = ','
-- variables for use in the update
DECLARE @accumulator VARCHAR(100),
@reference VARCHAR(15)
-- fill the details column with the list
UPDATE @codes_to_descriptions
SET @Accumulator = list = ISNULL(@Accumulator, '') + ', ' + single,
@Reference = row_id --an "anchor" whether used or not
-- return the one with all the data
RETURN (SELECT SUBSTRING(list, 2, LEN(list)-1) FROM @codes_to_descriptions WHERE row_id = (SELECT MAX(row_id) FROM @codes_to_descriptions))
END
[/font]
All you need after creating this function is to run:
[font="Courier New"]SELECT
customer,
dbo.fnConvertCodeToDescription(details)
FROM
accounts[/font]
This is also a problem-specific solution. I recommend reading the articles to get an better understanding of what is going on in the function.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 27, 2008 at 2:52 pm
THANK YOU! I read through the articles (ok, it took me about 3 reads to understand the theory) and they are very helpful. I've tweeked the code a bit to include a few other details in the case statement and moved the numbers table (i.e. tally table from the article) to another dev. db so i can use it for other queries. Thanks again for your assistance and for pointing me to techniques that I will use in the future!!
May 27, 2008 at 7:56 pm
Great I am glad I could provide some helpful information
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply