January 4, 2011 at 11:20 pm
Hello,
I need to bring the distinct result in third column
Below one is my sample table
Declare @t table
(
Id int,
name varchar(50),
Actions varchar(50),
Active int
)
Insert into @t
Select 1,'One','aabccdddee',1 Union all
Select 2,'Two','bbccce',1 Union all
Select 3,'Three','fdeg',1 Union all
select 4,'Four','ggeefff',1
Select * from @t
My Required Output is
1One abcde 1
2Two bce1
3Three fdeg 1
4Four gef1
can any one guide me through sample code
January 5, 2011 at 3:41 am
Hi
Borrowing abit off code from various post in http://www.sqlservercentral.com/Forums/Topic1042310-149-2.aspx and modifying it a bit. Thanks to Jeff Moden and GSquared.
You could do something like
create function a (@actions varchar(50)) returns varchar(50)
begin
DECLARE @CleanedText VARCHAR(50)
;with
a1 as (select 1 as N union all select 1 union all
select 1 union all select 1 union all
select 1 union all select 1 union all
select 1 union all select 1 union all
select 1 union all select 1),
a2 as (select 1 as N from a1 as a cross join a1 as b),
Tally as (select top (50) row_number() over (order by N) as N from a2)
select @CleanedText = (
select x from (select SubString(@actions, Tally.N, 1) x, Min(tally.n) n
from Tally
WHERE Tally.N <= LEN(@actions)
group by SubString(@actions, Tally.N, 1)
) y
order by n
for xml path(''), TYPE).value('.', 'varchar(50)')
RETURN @CleanedText
end
Though slightly better names would be good.
Running
Select *, dbo.a(actions) from @t
gives me the result your after. But i wouldnt want to run that on ALOT of rows. Performance would be bad.
Though i must say that storing multiple actions in a single column aint good. They should have their own table.
/T
January 5, 2011 at 3:59 am
First we'd need to know what you define as being "distinct"...
The sample data and your expected output is not clear enough on that.
My guess would be that this is a simple GROUP BY issue.
This will raise another question: is this some kind of test or homework?
January 5, 2011 at 4:43 am
For eg:
Declare @t table( Id int, name varchar(50), Actions varchar(50), Active int)
Insert into @t
Select 1,'One','aabccdddee',1 Union all
Select 1,'One','abcf',1 Union all
Select 2,'Two','bbccce',1 Union all
Select 2,'Two','bce',1 Union all
Select 3,'Three','fdeg',1 Union all
select 4,'Four','ggeefff',1
Right now am getting out put is
1Oneaabccdddee1
1Oneabcf 1
2Twobbccce 1
2Twobce 1
3Threefdeg 1
4Fourggeefff 1
My required result is below
1Oneabcdef1
2Twobce 1
3Threefdeg 1
4Fourgef 1
Result is combination of ID and distinct character in Actions column
For eg:
If actions column Hold the value like aabccdddee-
Instead of above need to distinct the character abcde
January 5, 2011 at 5:22 am
First of all, I apologize for assuming the task being homework... 😉
Using tommy's Tally table approach, here is a set based solution:
;WITH
a1 AS (SELECT 1 AS N UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1),
a2 AS (SELECT 1 AS N FROM a1 AS a CROSS JOIN a1 AS b),
Tally AS (SELECT TOP (50) ROW_NUMBER() OVER (ORDER BY N) AS N FROM a2)
,
cte_splitData AS
(
SELECT Id,name,SUBSTRING(Actions,N,1) AS val,MIN(n) AS x
FROM @t
CROSS APPLY Tally
WHERE N <= LEN(Actions)
GROUP BY Id,name,SUBSTRING(Actions,N,1)
)
SELECT
ID, name,
(SELECT '' + val
FROM cte_splitData t2
WHERE t2.ID = t1.ID
ORDER BY x
FOR XML PATH(''), TYPE
).value('./text()[1]', 'NVARCHAR(2000)')
FROM
cte_splitData t1
GROUP BY ID,name
ORDER BY id,name
The main difference: unlike tommy's function it's not called once per row. It'll shuffle around all data at once.
The only difference to your expected output: The position of the character 'f' for id=1 is at another position. This is caused by the identical id and name values for the two rows.
If the position matters, you'd either need to provide a unique row identifier or we'd need another subquery to add such an identifier using the ROM_NUMBER() function.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply