September 13, 2009 at 9:19 pm
I have a select statement that I would like to contain the pipe delimited result set as a row. Basically here is how the data would look in the end:
JOHN DOE red,3|orange,1|green,2|blue,4
SALLY SMITH pink,6|green,2
DAN JONES purple,7|green,2|red,3|yellow,8|black,9
My problem is one of syntax. Here is what I am trying to do (just pseudo code of course):
DECLARE @cvs AS varchar(10)
SELECT firstname, lastname, personpk, (SELECT @csv = '|' + SELECT colors.preference from colors where colors.personid = personpk) AS mycolors
This is a quick example off the top of my head (and I am sure it is laughable), but if someone can lead me in the right direction I would appreciate it. I have been banging my head on this for hours and can't seem to get it even close.
September 13, 2009 at 9:41 pm
Since you didn't post any sample data script I made you a little sample that'll work on any DB. On large tables I use another trick, using XML variables, but let's start with basics first...
First, you have to use a function that returns your CSV string. I left the separator as a configurable parameter:
CRAETE FUNCTION dbo.COLUMN_LIST
(
@Table_Name SYSNAME, @Delimiter varchar(10)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Out NVARCHAR(MAX)
SELECT @Out = ISNULL(@Out, '') + @Delimiter + Column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @Table_Name
RETURN SUBSTRING(@Out,2,LEN(@Out))
END
GO
Then, you call this function for every row in your table:
SELECT TABLE_NAME, DBO.COLUMN_LIST(TABLE_NAME, ' | ') AS COLUMNS_IN_TABLE
FROM INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_NAME
September 13, 2009 at 9:45 pm
thanks a ton! I will give it a whirl right now and see if I can finally put this puppy to bed.
September 13, 2009 at 10:21 pm
thanks a ton benyos.
However, after I created the function, it is only returning the 1st value, not the entire list of items that qualify.
I am getting "1|green" now. My goal is to get to "1,green|2,red|5,yellow|"
Without going into all of the tables involved, here is the key one for the option list I am trying to build:
ALTER FUNCTION FETCH_ROLE_OPTIONS
(
@ROLEATTRIBID int
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Out NVARCHAR(MAX)
SELECT @Out =
CONVERT(VARCHAR(MAX),rao.roleattribopt_pk) + ',' + rao.roleattribopt_option_text + '|'
FROM role_attrib_options rao
WHERE rao.roleattribopt_roleattrib_fk = @ROLEATTRIBID
RETURN @OUT
END
GO
I must be missing the part that makes this puppy loop through all of the values that meet the condition.
September 14, 2009 at 8:42 am
any ideas on why this only returns a single record?
September 14, 2009 at 12:15 pm
I guess I am going to have to use a cursor unless someone has a better idea.
September 14, 2009 at 7:49 pm
this is a curse. I can't get the cursor to work either.
ALTER FUNCTION [dbo].[fetch_role_options]
(
@ROLEATTRIBID int
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Out NVARCHAR(MAX)
DECLARE @row NVARCHAR(50)
DECLARE c CURSOR FOR
(SELECT (CONVERT(VARCHAR(MAX),roleattribopt_pk) + ',' + roleattribopt_option_text + '|') AS options
FROM role_attrib_options WHERE roleattribopt_roleattrib_fk = @ROLEATTRIBID)
OPEN c
FETCH NEXT FROM c INTO @row
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Out = @Out + @row
END
CLOSE c
DEALLOCATE c
RETURN @OUT
END
I wish I didn't have to use a cursor, but getting this one column to be a pipe delimited list will be the apparently death of me.
September 16, 2009 at 8:40 am
We've had success with this using cross apply. Please see the following pseudocode:
SELECT
firstname,
lastname,
personpk,
ColorsList = LEFT(ColorList.list, LEN(ColorList.list)-1)
from persons
--
-- Colors as a single column
--
CROSS APPLY
(
SELECT
Color + ''|'' AS [text()]
FROM Colors
WHERE colors.personid = personpk
FOR XML PATH('''')
) ColorsList (list)
September 16, 2009 at 9:29 am
VERY interesting! I will give this a whirl and see if it gets me to the happy place. Your example is very close to what I am trying to do.
September 16, 2009 at 9:37 am
I hope it helps you to find your happy place. Good luck!
September 18, 2009 at 11:59 am
scott detweiler (9/13/2009)
thanks a ton benyos.However, after I created the function, it is only returning the 1st value, not the entire list of items that qualify.
I am getting "1|green" now. My goal is to get to "1,green|2,red|5,yellow|"
Without going into all of the tables involved, here is the key one for the option list I am trying to build:
ALTER FUNCTION FETCH_ROLE_OPTIONS
(
@ROLEATTRIBID int
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Out NVARCHAR(MAX)
SELECT @Out =
CONVERT(VARCHAR(MAX),rao.roleattribopt_pk) + ',' + rao.roleattribopt_option_text + '|'
FROM role_attrib_options rao
WHERE rao.roleattribopt_roleattrib_fk = @ROLEATTRIBID
RETURN @OUT
END
GO
I must be missing the part that makes this puppy loop through all of the values that meet the condition.
You missed part of the solution that was supplied by dbo.benyos. When you modified it you didn't include the variable on the right side of the equal sign. This is how it accumulates all the rows. With the way you have the rows retrieved are assigned 1 at a time to that variable. Then the last row that is assigned is what you see.
You need to have it similar to the following:
SELECT @Out = ISNULL(@Out, '') +
CONVERT(VARCHAR(MAX),rao.roleattribopt_pk) + ',' + rao.roleattribopt_option_text + '|'
FROM role_attrib_options rao
WHERE rao.roleattribopt_roleattrib_fk = @ROLEATTRIBID
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply