February 11, 2009 at 1:32 pm
Hi all,
Fairly new to SQL Server and have inherited the following.
Table A contains a column that holds a series of comma separated id's that are the pk's of another table. I need to create a procedure to return a rowset that holds some Table A columns plus a comma separated descripton column from the matching rows in Table B.
This fictituous example may help:
CREATE TABLE tbl_Country
(
CountryName nvarchar(50),
FlagColors nvarchar(50)
)
CREATE TABLE tbl_Colors
(
ColorID int,
ColorName nvarchar(50)
)
INSERT INTO tbl_Country
VALUES ('USA', '1001,1002,1003')
INSERT INTO tbl_Country
VALUES ('Canada', '1001,1002')
INSERT INTO tbl_Colors
VALUES (1001, 'Red')
INSERT INTO tbl_Colors
VALUES (1002, 'White')
INSERT INTO tbl_Colors
VALUES (1003, 'Blue')
The result set I'm trying to acheive would show:
'USA','Red, White, Blue'
'Canada','Red, White'
While this seems like a crazy table design, it also seems to me it should be doable. However, I've gone down a number of dead ends and my jaw's clenching tighter with each failure.
Any advice would be appreciated.
Kevin
February 11, 2009 at 2:18 pm
here's a function that will do it.
use global
go
ALTER function [dbo].[fListToVarchars]( @list varchar(max), @delim varchar(6) )
returns @returnTable table
( item varchar(2048) not null, itemSequence smallint not null )
as begin
declare @xml XML
set @xml = char(60)+'item>' + REPLACE(REPLACE(REPLACE(@list,char(38),'&'),char(60),'<'), @delim,char(60)+'/item>'+char(60)+'item>') + char(60)+'/item>'
insert into @returnTable
SELECT ltrim(data.item.value('.','varchar(2048)')), row_number() over (order by getdate())
FROM @xml.nodes('//item') as data(item)
return
end
-- note: the datatype @returnTable.item must match the second
-- parameter to data.item.value()
go
usage:
select *
from tbl_Country as A cross apply global.dbo.fListToVarchars(A.FlagColors, ',') as X
join tbl_Colors B on B.ColorId = cast( X.item as int )
February 11, 2009 at 2:42 pm
Thanks for this. I'll try it out as soon as I get a chance.
Kevin
February 11, 2009 at 5:06 pm
Solution using Cursors
------------------------------
CREATE FUNCTION dbo.ParseList
(
@ValueList VARCHAR(8000)
, @Delimiter VARCHAR(1)
)
RETURNS @ParsedListValues TABLE
(
ValueID INT IDENTITY(1, 1) NOT NULL
, Value VARCHAR(4000) NOT NULL
)
AS
BEGIN
Begin
DECLARE
@EndPosition INT
, @StartPosition INT
, @SectionLength INT
, @SubList VARCHAR(4000)
, @Remainder VARCHAR(4000)
, @Value VARCHAR(4000)
SET @StartPosition = 1
SET @Remainder = ''
IF CHARINDEX(@Delimiter, @ValueList) > 0
Begin
WHILE @StartPosition <= DATALENGTH(@ValueList) / 2
Begin
SET @SectionLength = 4000 - DATALENGTH(@ValueList) / 2
SET @SubList = @Remainder + SUBSTRING(@ValueList, @StartPosition, @SectionLength)
SET @StartPosition = @StartPosition + @SectionLength
SET @EndPosition = CHARINDEX(@Delimiter, @SubList)
WHILE @EndPosition > 0
Begin
SET @Value = LTRIM(RTRIM(LEFT(@SubList, @EndPosition -1)))
INSERT INTO @ParsedListValues
(Value)
VALUES
(@Value)
SET @SubList = substring(@SubList, @EndPosition + 1, len(@SubList))
SET @EndPosition = charindex(@Delimiter, @SubList)
End
SET @Remainder = @SubList
End
INSERT INTO @ParsedListValues
(Value)
VALUES(LTRIM(RTRIM(@Remainder)))
End
ELSE
Begin
SET @Value = @ValueList
INSERT INTO @ParsedListValues
(Value)
VALUES(LTRIM(RTRIM(@ValueList)))
End
End
RETURN
END
---------------------------------------------
ALTER FUNCTION [dbo].[fCountry_FlagColor]()
RETURNS @returnTable table( CountryName nvarchar(50) not null, FlagColors nvarchar(50))
AS BEGIN
DECLARE @CountryName nvarchar(50)
DECLARE @List nvarchar(50)
DECLARE @CLR nvarchar(50)
SET @CLR=''
DECLARE CUR CURSOR FOR SELECT CountryName FROM tbl_Country
OPEN CUR
FETCH NEXT FROM CUR INTO @CountryName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @List=FlagColors from tbl_country WHERE CountryName=@CountryName
SELECT @CLR=@Clr+ColorName+','
FROM dbo.ParseList(@List,',') A
INNER JOIN tbl_Colors B
On A.Value=B.ColorID
SET @CLR=LEFT(@Clr,LEN(@Clr)-1)
INSERT @returnTable
SELECT @CountryName,@Clr
SET @CLR=''
FETCH NEXT FROM CUR INTO @CountryName
END
RETURN
END
----------------------------------------------------------
Query
SELECT * FROM dbo.fCountry_FlagColor()
February 11, 2009 at 6:05 pm
antonio.collins (2/11/2009)
here's a function that will do it.
use global
go
ALTER function [dbo].[fListToVarchars]( @list varchar(max), @delim varchar(6) )
returns @returnTable table
...
i updated the function to be an inline table valued function since they perform better than multi-statement functions.
alter function [dbo].[fListToVarchars]( @list varchar(max), @delim varchar(6) )
returns table
as return
(
with X(data) as (select cast( char(60)+'item>'
+ REPLACE(REPLACE(REPLACE(@list,char(38),'&'),char(60),'<'),
@delim,char(60)+'/item>'+char(60)+'item>')
+ char(60)+'/item>' as XML) as data)
select ltrim(data.item.value('.','varchar(2048)')) as item,
row_number() over (order by getdate()) as itemSequence
from X
cross apply X.data.nodes('//item') as data(item)
)
create a seperate version to handle ints by changing the item.value() parameters:
alter function [dbo].[fListToInts]( @list varchar(max), @delim varchar(6) )
returns table
as return
(
with X(data) as (select cast( char(60)+'item>'
+ REPLACE(REPLACE(REPLACE(@list,char(38),'&'),char(60),'<'),
@delim,char(60)+'/item>'+char(60)+'item>')
+ char(60)+'/item>' as XML) as data)
select ltrim(data.item.value('.','int')) as item,
row_number() over (order by getdate()) as itemSequence
from X
cross apply X.data.nodes('//item') as data(item)
)
this function is great companion.
ALTER function [dbo].[fGetGroupsOfVarcharsIndexed]( @list varchar(max),
@groupSeperator varchar(16), @groupDelim varchar(16), @itemDelim varchar(16) )
returns @returnTable table
( groupName varchar(255), groupSequence smallint,
list varchar(4096), item varchar(255), itemSequence smallint,
primary key (groupName, groupSequence)
)
as
begin
insert into @returnTable
select y.groupName, y.groupSequence, y.list, z.item, z.itemSequence
from (
select
left( item, charindex( @groupSeperator, item ) - 1 ) as groupName,
itemSequence as groupSequence,
right( item, datalength(item) - charindex( @groupSeperator, item )
+ datalength(@groupSeperator) - 1 ) as list
from
global.dbo.fListToVarchars( nullif(nullif(@list,''),'*'), @groupDelim ) as x ) as y
cross apply global.dbo.fListToVarchars( nullif(nullif(y.list,''),'*'), @itemDelim) as z
return
end
usage:
select *
from global.dbo.fGetGroupsOfVarchars(
'STATE:FL,GA,SC;CLASS:A,C;MONTH:200710,2000711,200712,200801',
':',';',',')
[font="Courier New"]
groupNamelistitemitemSequence
groupSequence
--------------------------------------------------------------------
MONTH3200710,200711,200712,2008012007101
MONTH3200710,200711,200712,2008012007112
MONTH3200710,200711,200712,2008012007123
MONTH3200710,200711,200712,2008012008014
CLASS2A,CA1
CLASS2A,CC2
STATE1FL,GA,SCFL1
STATE1FL,GA,SCGA2
STATE1FL,GA,SCSC3
[/font]
we use these functions to pass mutli-value selections from web pages/windows forms to queries. very fast and very flexible since you can easily count the number of selections per category, pick only the first or second selection, etc.
February 11, 2009 at 7:25 pm
This is SQL Server 2005... with that in mind, you don't need the overhead of UDF's and you sure as heck don't need a cursor or While loop for such a task.
;WITH
cteSplit AS
(--==== Split out the individual ColorID's from the FlagColors of each country
SELECT c.CountryName,
SUBSTRING(','+c.FlagColors, t.Number+1, CHARINDEX(',', c.FlagColors+',', t.Number)-t.Number) AS ColorID
FROM Master.dbo.spt_Values t
CROSS JOIN dbo.tbl_Country c
WHERE t.Number <= LEN(','+c.FlagColors)
AND SUBSTRING(','+c.FlagColors, t.Number, 1) = ','
AND t.Type = 'P'
AND t.Number BETWEEN 1 AND 52
)
,
cteColors AS
(--==== Convert the splitout ColorID's to actual color names
SELECT s.CountryName,
c.ColorName
FROM cteSplit s
INNER JOIN dbo.tbl_Colors c
ON s.ColorID = c.ColorID
)
--===== And, finally, concatenate all of the color names for each country
SELECT c1.CountryName,
STUFF((SELECT ',' + c2.ColorName FROM cteColors c2 WHERE c1.CountryName = c2.CountryName FOR XML PATH('')),1,1,'') AS Colors
FROM cteColors c1
GROUP BY c1.CountryName
Output comes to be...
[font="Courier New"]
CountryName Colors
----------- --------------
Canada Red,White
USA Red,White,Blue
[/font]
I do strongly recommend building a Tally table instead of using spt_Values. Here's the link...
http://www.sqlservercentral.com/articles/TSQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply