function with table value --group by?

Question

Post reply

function with table value --group by?

ferrarielly

Valued Member

Points: 50
More actions
July 4, 2012 at 2:01 am

#263263

Hi,
I've this funcion (in a function with table value)
ALTER FUNCTION [dbo].[ProvaFn]()
RETURNS @Results TABLE (Progr decimal(18,0),
codcli varchar(7),
DOCTYPE varchar(3),
NUMBER decimal(18,0)
Insert into @Results (Progr,codcli ,DOCTYPE,NUMBER ...
..
My problem is that I would like to obtain a table grouped by codcli filed..but I don't know how to do that..
Someone could help me?
🙂
July 4, 2012 at 3:26 am

This was removed by the editor as SPAM
July 4, 2012 at 3:49 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ferrarielly Valued Member Points: 50 More actions · Answer 1

Hi,

the function is the following:

ALTER FUNCTION [dbo].[LI_Vista_RigheEvadibili_FiltrataFn]()

RETURNS @Results TABLE (Progr decimal(18,0),

codclifor varchar(7),

DOCTYPE varchar(3),

NUMDOC decimal(18,0),

AS

insert into @Results (Progr,codclifor ,DOCTYPE,

NUMDOC)

(select td.Progr,td.codclfor,

td.DOCTYPE,td.NUMDOC,

where extd.maglogisticaT=1 and

td.TIPODOC in ('AAA','BBB')

return

end

The td table is like:

ProgrYear NumDocDocTypeCodCliFor

39572009 25AAAC 2

39582009 26bbbC 2

39602009 28bbbC 3

39612009 29 AAAC 3

I need that the records saved in the @result variable (or in this virtual table generated bu this function) are grouped by CodCliFor

So I need to have the doc grouped by the CodCliFor

ferrarielly Valued Member Points: 50 More actions · Answer 2

So I cannot do that? Even if I use another table to store value or have you some idea?

Thanx

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

ferrarielly (7/4/2012)
Hi,
the function is the following:
ALTER FUNCTION [dbo].[LI_Vista_RigheEvadibili_FiltrataFn]()
RETURNS @Results TABLE (Progr decimal(18,0),
codclifor varchar(7),
DOCTYPE varchar(3),
NUMDOC decimal(18,0),
AS
insert into @Results (Progr,codclifor ,DOCTYPE,
NUMDOC)
(select td.Progr,td.codclfor,
td.DOCTYPE,td.NUMDOC,
where extd.maglogisticaT=1 and
td.TIPODOC in ('AAA','BBB')
return
end
The td table is like:
ProgrYear NumDocDocTypeCodCliFor
39572009 25AAAC 2
39582009 26bbbC 2
39602009 28bbbC 3
39612009 29 AAAC 3
I need that the records saved in the @result variable (or in this virtual table generated bu this function) are grouped by CodCliFor
So I need to have the doc grouped by the CodCliFor

Grouping by CodCliFor to return two rows requires that the other columns in the output list are subjected to an aggregate function. What do you want to do with them? MIN()? MAX()

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden