November 2, 2015 at 4:41 pm
I have data laid out like this:
Tablename: variations
groupitem|variationname|size|color
groupitem1|variation1|Small|Black
groupitem1|variation2|Medium|Black
groupitem1|variation3|Large|Black
groupitem1|variation4|X-Large|Black
groupitem1|variation5|Small|Blue
groupitem1|variation6|Medium|Blue
groupitem1|variation7|Large|Blue
groupitem1|variation8|X-Large|Blue
I need a function that I can run a select like this:
SELECT DISTINCT groupitem, function([groupitem])
FROM variations
I want the resulting data to look like this:
groupitem|storedprocedureresult
groupitem1|Small;Medium;Large;X-Large
Here's what I have so far:
CREATE FUNCTION dbo.function(@groupitem varchar(20))
RETURNS VARCHAR(5000)
AS
BEGIN
DECLARE @Children VARCHAR(5000)
SELECT @Children = Size + ISNULL(';' + @Children, '')
FROM variations
WHERE groupitem = @groupitem
ORDER BY Size
RETURN @Children
END
But the resulting data is showing as follows:
groupitem|functionresult
groupitem1|Small;Small;Medium;Medium;Large;Large;X-Large;X-Large
Basically I need it to only select the distinct values and list them horizontally. I can't figure out the distinct part.
Any help would be greatly appreciated.
November 2, 2015 at 4:52 pm
Hi and welcome to the forums
It's easier for people to help you if the sample data is in an easily usable state. I'm sitting in a presentation at the moment, so I have some time.
CREATE TABLE TableName (
GroupName VARCHAR(50),
variationname VARCHAR(50),
Size VARCHAR(50),
Colour VARCHAR(50)
);
INSERT INTO dbo.TableName
(GroupName,
variationname,
Size,
Colour
)
VALUES ('groupitem1','variation1','Small','Black'),
('groupitem1','variation2','Medium','Black'),
('groupitem1','variation3','Large','Black'),
('groupitem1','variation4','X-Large','Black'),
('groupitem1','variation5','Small','Blue'),
('groupitem1','variation6','Medium','Blue'),
('groupitem1','variation7','Large','Blue'),
('groupitem1','variation8','X-Large','Blue')
I'm going to recommend you don't use a function, as functions are notoriously slow in SQL Server. It'll work, but it'll work slow.
I'll write you up a single query to get the results you want, if you absolutely want a slow function, it should be easy enough to convert the single query to a query and function.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 2, 2015 at 4:56 pm
Thank you. I'd love a simple query if there is one. That'd be way better. I just couldn't see how to do this without some sort of function.
I'm all ears. Thank you!!!
November 2, 2015 at 5:10 pm
CREATE TABLE TableName (
GroupName VARCHAR(50),
variationname VARCHAR(50),
Size VARCHAR(50),
SizeOrder INT,
Colour VARCHAR(50)
);
INSERT INTO dbo.TableName
(GroupName,
variationname,
Size,
SizeOrder,
Colour
)
VALUES
('groupitem1','variation1','Small', 1,'Black'),
('groupitem1','variation2','Medium', 2, 'Black'),
('groupitem1','variation3','Large', 3,'Black'),
('groupitem1','variation4','X-Large', 4,'Black'),
('groupitem1','variation5','Small',1,'Blue'),
('groupitem1','variation6','Medium',2,'Blue'),
('groupitem1','variation7','Large',3,'Blue'),
('groupitem1','variation8','X-Large',4,'Blue');
WITH Distincted AS (SELECT DISTINCT GroupName, Size, SizeOrder FROM dbo.TableName)
SELECT DISTINCT GroupName, LEFT(SizeList, LEN(SizeList)-1) AS SizeList
FROM dbo.TableName t
CROSS APPLY (SELECT Size + ', '
FROM Distincted t_inner
WHERE t.GroupName = t_inner.GroupName
ORDER BY SizeOrder
FOR
XML PATH('')
) AS Sizes (SizeList)
I had to add a column to define what the order of the values within the string should be. Without that, you'll get them im alphabetical order (Large, Medium, Small, X-Large). What I'd suggest is that you create a "Sizes" table with the size names and order and put a foreign key between that and the source table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 3, 2015 at 8:07 am
What the SQL wizardry?!?! I'm still trying to figure out what you did there, but it works. I've been able to apply the logic to my specific application and it gives me what I want.
Apparently I have a long way to be on my SQL Padawan journey 🙂
November 3, 2015 at 8:22 am
Hmmmm. Since I don't fully know what you've done, I'm having a hard time plugging this into the situation I need it to work within. I am actually updating records in a table with the select results of the SQL statement you created for me. it needs to look something like this:
UPDATE targettable SET SizeList = x.SizeList
FROM
(
WITH Distincted AS (SELECT DISTINCT GroupName, Size, SizeOrder FROM dbo.TableName)
SELECT DISTINCT GroupName, LEFT(SizeList, LEN(SizeList)-1) AS SizeList
FROM dbo.TableName t
CROSS APPLY (SELECT Size + ', '
FROM Distincted t_inner
WHERE t.GroupName = t_inner.GroupName
--ORDER BY SizeOrder
FOR
XML PATH('')
) AS Sizes (SizeList)
)x
WHERE x.GroupName = targettable.GroupName
This obviously doesn't work, or else I wouldn't be posting this follow up question. I've never seen the method that you created, so I'm unsure how to apply it.
Thanks in advance.
November 3, 2015 at 8:51 am
trevor.feller (11/3/2015)
Hmmmm. Since I don't fully know what you've done, I'm having a hard time plugging this into the situation I need it to work within. I am actually updating records in a table with the select results of the SQL statement you created for me. it needs to look something like this:
UPDATE targettable SET SizeList = x.SizeList
FROM
(
WITH Distincted AS (SELECT DISTINCT GroupName, Size, SizeOrder FROM dbo.TableName)
SELECT DISTINCT GroupName, LEFT(SizeList, LEN(SizeList)-1) AS SizeList
FROM dbo.TableName t
CROSS APPLY (SELECT Size + ', '
FROM Distincted t_inner
WHERE t.GroupName = t_inner.GroupName
--ORDER BY SizeOrder
FOR
XML PATH('')
) AS Sizes (SizeList)
)x
WHERE x.GroupName = targettable.GroupName
This obviously doesn't work, or else I wouldn't be posting this follow up question. I've never seen the method that you created, so I'm unsure how to apply it.
Thanks in advance.
The CTE needs to be first. While I don't think it's absolutely necessary to include your update table in the FROM clause, I think it's a good idea to do so, because it makes it easier to understand the relationships. Try the following:
WITH Distincted AS (SELECT DISTINCT GroupName, Size, SizeOrder FROM dbo.TableName)
UPDATE targettable SET SizeList = x.SizeList
FROM targettable
INNER JOIN
(
SELECT DISTINCT GroupName, LEFT(SizeList, LEN(SizeList)-1) AS SizeList
FROM dbo.TableName t
CROSS APPLY (SELECT Size + ', '
FROM Distincted t_inner
WHERE t.GroupName = t_inner.GroupName
--ORDER BY SizeOrder
FOR
XML PATH('')
) AS Sizes (SizeList)
)x
ON x.GroupName = targettable.GroupName
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 3, 2015 at 9:15 am
You're right on with the small correction of the joining criteria at the bottom is WHERE instead of ON.
Works perfectly!!!
November 3, 2015 at 9:20 am
If you don't use the Order By, you will get a non-deterministic order for the concatenated string values. If you need the values concatenated in a specific order, you need to have something that you're ordering by.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 3, 2015 at 9:38 am
In case of interest we always Alias the table-being-updated as "U" so that we know which table, ina multi-JOIN-query, is the target:
UPDATE [highlight="#ffff11"]U[/highlight]
SET SizeList = x.SizeList
FROM targettable[highlight="#ffff11"] AS U[/highlight]
INNER JOIN
...
Others prefer to uses Aliases with a mnemonic/shortname indicating the table itself.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply