October 1, 2002 at 7:33 am
Hi there!
Can this be done by other way(s)?
Thanxs!
Scenario:
Table1
-----------
NrPedido (UniqueID)
Subject
Description
(...)
Table2
---------
UniqueID
*TB1_NrPedido
Dept_UniqueID
DAMW_FIELD (CodDestino on Cursor example)
(...)
I need to acomplish this Querie:
SELECT (
[
Parse here a CSV string
with "DAMW_FIELD" field for each
NrPedido on Table1!
]
) AS sDAMW_FIELD FROM Table1
So far i'm doing that with a CURSOR, but is slow...
Any ideias???
Example:
------------------------------------
CREATE FUNCTION sParseCodDist(@object_name varchar(255))
RETURNS varchar(255) AS
BEGIN
-- Declare the variables to store the values returned by FETCH.
DECLARE @f_type varchar(255), @t varchar(255)
SET @t=''
DECLARE table1_cursor CURSOR FOR
SELECT CodDestino FROM dbo.vst_MultiDist
WHERE NrPedido =@object_name
order by CodDestino
OPEN table1_cursor
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns in the select statement.
FETCH NEXT FROM table1_cursor
INTO @f_type
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Concatenate and display the current values in the variables.
SET @t =@t + @f_type +'|'
-- print @f_type
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM table1_cursor
INTO @f_type
END
SET @t = +'|' + @t
CLOSE table1_cursor
DEALLOCATE table1_cursor
RETURN @t
END
-------------------------------- End of Function --------------------------------
USE:
select (dbo.sParseCodDist(NrPedido)) as ParsedValues from Table1 where NrPedido >= 490
RESULTS:
ParsedValues
=======================
|9|
|1|
|1|10|26|
|1|8|13|14|15|17|19|25|
=======================
-----------------------------------
Edited by - labdev on 10/01/2002 07:38:41 AM
~~ @Rodrigues ~~
October 1, 2002 at 8:50 am
What you want is to perform a pivot table fuction. There are some examples of doing pivot table queries on my website at http://www.geocities.com/sqlserverexamples/#pivot.
I think you will want to do something like for you function, although possible you can
get away without the function altogether.
CREATE FUNCTION sParseCodDist(@object_name varchar(255))
RETURNS varchar(255) AS
BEGIN
DECLARE @t varchar(255)
SET @t=''
SELECT @t =@t + CodDestino +'|'
FROM dbo.vst_MultiDist
WHERE NrPedido =@object_name
order by CodDestino
RETURN @t
END
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 1, 2002 at 2:10 pm
Thanxs a lot Larsen 🙂
Why use a cursor if a Function() do the job?
PS: Cool site you have... saved some examples for later "investigation"!
~~ @Rodrigues ~~
October 18, 2002 at 4:05 am
There is an easy method in the scripts secion of this site.
Here is the link
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply