April 12, 2013 at 1:44 pm
I've got data that can be similar for a specific account, yet different (one of those "it depends")
I need to be able to extract the single account record with all of the distinct fuel types associated with it. In the case they are duplicated, I don't need the duplicated fuel type. In cases where the fuel types are different I need to be able to include it into the same string
Consider:
MerchNum | FuelType
00000026104Diesel;Gas;Other - Non Fuel;
00000026104Diesel
0000000500444Gas
0000000500444Biodiesel
What I need to see is:
MerchNum | FuelType
00000026104Diesel;Gas;Other - Non Fuel;
0000000500444Gas;Biodiesel
Here is some test data:CREATE TABLE #Data (AcctNum varchar(20), FuelType varchar(128))
INSERT INTO #Data VALUES ('00000026104', 'Diesel;Gas;Other - Non Fuel;')
INSERT INTO #Data VALUES ('00000026104', 'Diesel')
INSERT INTO #Data VALUES ('0000000500444', 'Gas')
INSERT INTO #Data VALUES ('0000000500444', 'Biodiesel')
Bearing in mind the TSQL for this may have to parse thousands of records, does anyone have an idea how I would go about getting the result?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 12, 2013 at 1:51 pm
love to figure this one out!
nice!
i split the data with DelimitedSply8K, then grouped it, and then used FORXML to reassemble a new string:
/*
AcctNumFuelTypes
0000000500444Biodiesel,Gas
00000026104Diesel,Gas,Other - Non Fuel
*/
With MyGroupedData
AS
(
--split it up and group for unique values
select
#Data.AcctNum,
Myfn.Item
FROM #Data
CROSS APPLY dbo.DelimitedSplit8K(FuelType,';') Myfn
WHERE Item <> ''
Group By
#Data.AcctNum,
Myfn.Item
)
--re-assemble the strings , one per AcctNum
SELECT AcctNum,stuff(( SELECT ';' + Item
FROM MyGroupedData s2
WHERE s2.AcctNum= s1.AcctNum --- must match GROUP BY below
ORDER BY s1.Item
FOR XML PATH('')
),1,1,'') as [FuelTypes]
FROM MyGroupedData s1
GROUP BY s1.AcctNum --- without GROUP BY multiple rows are returned
ORDER BY s1.AcctNum
Lowell
April 12, 2013 at 1:54 pm
Here is one way using DelimitedSplit8K.
;with split as
(
select AcctNum, Item
from #Data
cross apply dbo.DelimitedSplit8K(FuelType, ';')
)
, SingleVals as
(
select distinct * from split
where Item > ''
)
select AcctNum,
STUFF((select ';' + Item
from SingleVals s2
where s1.AcctNum = s2.AcctNum
order by s2.Item
for XML PATH('')), 1, 1, ' ')
from SingleVals s1
group by AcctNum
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 12, 2013 at 1:55 pm
LOL I should have refreshed. Seems that Lowell beat me to the punch again.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 12, 2013 at 1:58 pm
good god that is like the 200th post where we proposed the same solution with only minor differences between our code example!
you usually beat me to the punch anyway!
Lowell
April 12, 2013 at 2:01 pm
Thanks so much guys 🙂
My brain just doesn't think that way...very simplistic and I REALLY APPRECIATE IT!!!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 12, 2013 at 2:01 pm
It is pretty scary!! After seeing yours I modified mine slightly to avoid the second cte which means our code is so close to the same it isn't even funny.
;with split as
(
select AcctNum, Item
from #Data
cross apply dbo.DelimitedSplit8K(FuelType, ';')
where Item > ''
)
select AcctNum,
STUFF((select ';' + Item
from split s2
where s1.AcctNum = s2.AcctNum
order by s2.Item
for XML PATH('')), 1, 1, ' ')
from split s1
group by AcctNum
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply