January 13, 2009 at 1:14 pm
Hi All,
My Query returns onecolumn
consider,
Column1
--------
Value1, Value2
Value1, Value3
Value1, Value2
Value4, Value5
my requirement is to get all the distinct values in the column1
Column1
---------
Value1
Value2
Value3
Value4
Value5
is there any possibility of doing this without using CURSOR
Please help in this. Thanks in advance.
January 13, 2009 at 2:08 pm
Yes, how many values can be in this column?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 13, 2009 at 2:36 pm
Maybe this will help: (based on http://qa.sqlservercentral.com/articles/XML/61618/)
DECLARE @test-2 TABLE (t1 VARCHAR(100))
INSERT INTO @test-2
SELECT 'Value1,Value2' UNION ALL
SELECT 'Value2' UNION ALL
SELECT 'Value2,Value3'
--IF more than 2 values per row can be entered
DECLARE @STR VARCHAR(MAX)
DECLARE @x XML
SELECT @STR = ISNULL(@str + ',','') + t1
FROM @test-2
SET @x = ' '
SELECT DISTINCT x.i.value('.', 'VARCHAR(20)') AS Item
FROM @x.nodes('//i') x(i)
--IF only 1 or 2 values per row
SELECT t1
FROM (SELECT SUBSTRING(t1,0,CHARINDEX(',', t1)) AS t1
FROM @test-2
UNION
SELECT SUBSTRING(t1,CHARINDEX(',', t1)+1, LEN(t1)-CHARINDEX(',', t1)+1)
FROM @test-2) t2
WHERE t1 != ''
January 13, 2009 at 7:01 pm
there is one column having many rows and each row can have any no of values randing from 1 to 'n'
January 13, 2009 at 7:29 pm
HI,
The above code is working perfectly as what i required. Thank you for providing this.
We need to use the same concept in SQL 2000 also, is there any possiblities to do the same thing in SQL 2000.
I think XML datatype is not supporting in SQL 2000
please help me.
January 14, 2009 at 5:21 am
Other way around is to split delimited values to rows and then take distinct of those values.
Jeff had written a nice article about using tally table to split delimited string into a table.
--Ramesh
January 15, 2009 at 12:19 am
Hi all
Thank you very much for you help.
I have done this using Cursors in SQL 2000.
DECLARE @tblRolesWithComma TABLE(Roles VARCHAR(8000))
insert into @tblRolesWithComma VALUES('Role1,Role2,')
insert into @tblRolesWithComma VALUES('Role1,Role3')
insert into @tblRolesWithComma VALUES('Role1,Role4')
insert into @tblRolesWithComma VALUES('Role2,Role5')
insert into @tblRolesWithComma VALUES('Role6,Role1')
insert into @tblRolesWithComma VALUES('Role7')
insert into @tblRolesWithComma VALUES('Role8')
SELECT * FROM @tblRolesWithComma
DECLARE @tblRolesInSingleColumn TABLE(RoleId VARCHAR(500))
DECLARE cur_Roles CURSOR
READ_ONLY
FOR (SELECT * FROM @tblRolesWithComma)
DECLARE @RoleIds VARCHAR(8000)
OPEN cur_Roles
declare @pos int
declare @RoleId VARCHAR(500)
FETCH NEXT FROM cur_Roles INTO @RoleIds
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
IF RIGHT(RTRIM(@RoleIds),1) <> ','
SET @RoleIds = @RoleIds + ','
SET @pos = PATINDEX('%,%' , @RoleIds)
WHILE @pos <> 0
BEGIN
SET @RoleId = left(@RoleIds, @pos - 1)
INSERT INTO @tblRolesInSingleColumn VALUES(CAST(@RoleId as varchar(500)))
SET @RoleIds = STUFF(@RoleIds, 1, @pos, '')
SET @pos = PATINDEX('%,%' , @RoleIds)
END
END
FETCH NEXT FROM cur_Roles INTO @RoleIds
END
CLOSE cur_Roles
DEALLOCATE cur_Roles
SELECT DISTINCT * FROM @tblRolesInSingleColumn
SET NOCOUNT OFF
GO
Once again thanks a lot for your help
--------
Santosh
January 15, 2009 at 12:34 am
This is the worst way to do it. Why would you choose to do it this way, when you already had two better ways to do it?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 15, 2009 at 12:37 am
Yes, you are correct it effects the performance.
Could you please tell the best logic, so i can use (in SQL 2000)
thanks in advance.
January 15, 2009 at 7:53 am
Follow Ramesh's link to Jeff Moden's article. Jeff is the "Sultan of Speed", and you are unlikely to find anything faster.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 15, 2009 at 7:54 am
Also, in the future, please post your SQL 2000 questions in the SQL 2000 forums.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 20, 2009 at 10:14 am
I send you something that hope help you
January 20, 2009 at 8:19 pm
See, this is the requirement that i want in 2000 as well as in 2005, thats I have posted here.
thank you.
January 21, 2009 at 7:42 am
Hi, this works with 2000 and 2005
Create Table #t2(ID int identity, Column1 nvarchar(100))
Create Table #t3(Column1 nvarchar(100))
insert into #t2
select 'Value1, Value2'
union all select 'Value1, Value3'
union all select 'Value1, Value2'
union all select 'Value4, Value5'
update #t2 set Column1 = replace(Column1,' ','')+','
DECLARE @Values nvarchar(100), @ID int
Select @ID = min(ID) from #t2
While @ID is not null
Begin
select @Values = substring(Column1, 1,patindex('%,%',Column1)) From #t2 where ID = @ID
While isnull(@Values,'') <> ''
begin
Insert Into #t3
Select @Values
update #t2 set Column1 = stuff(Column1, 1, len(@Values), '') where ID = @ID
select @Values = substring(Column1, 1,patindex('%,%',Column1)) From #t2 where ID = @ID
end
Select @ID = min(ID) from #t2 Where ID > @ID
End
update #t3 set Column1 = replace(Column1,',','')
select distinct *
from #t3
January 21, 2009 at 8:23 am
Greate Vicky,
Thank you very much for providing the solution.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply