Pass a table into a Function... Or another way.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pass a table into a Function... Or another way.

Author

Topic

rternier
Starting Member

44 Posts

Posted - 2007-07-03 : 14:47:12

I'm not sure if this can be done...

I have a Table:

FKID,ReferenceName, Value
====================
1, System.TextBox, 12
2, System.TextArea, HI, my name is Ryan
3, System.ListBox, 1
3, System.ListBox, 2
3, System.ListBox, 4
3, System.ListBox, 5

I want to take the System.Listbox rows and change it to:

3, System.ListBox, "1, 2, 4, 5"

Now, this would be so easy if I could pass a table into a Function, but I'm not sure that's possible.

Is it?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-03 : 15:03:51

use this in your select
http://weblogs.sqlteam.com/mladenp/archive/2007/06/01/60220.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

rternier
Starting Member

44 Posts

Posted - 2007-07-03 : 17:19:05

I looked at it but I can't see it :P

I'm not an expert at SQL so my knowledge is somewhat limited.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-04 : 04:20:51

try this. maybe you'll have to group differently but it should give you an idea.


select fkid, referenceName, 
       (SELECT  Value + ', ' as [text()] 
        FROM    YourTable 
        GROUP BY fkid, referenceName 
        ORDER BY Value FOR XML PATH('')) as listOfValues
from YourTable

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

rternier
Starting Member

44 Posts

Posted - 2007-07-04 : 14:31:56

THanks, I worked with that and I got closer to what I need:

---------------
INSERT INTO @LV(FieldID, ReferenceName, CSV)
SELECT f.FieldID, f.ReferenceName,
(SELECT CONVERT(VarChar(MAX),FLR.[FieldListValueID]) + ', '
FROM [Form].[Fields] F
INNER JOIN [Form].FieldListValues FLV ON FLV.FieldID = F.FieldID
INNER JOIN [Item].[ItemDetailFieldListRecords] FLR ON FLV.FieldListValueID = FLR.FieldListValueID
WHERE FLR.ItemDetailID = @ItemDetailID
ORDER BY FLR.[FieldListValueID] FOR XML PATH('')) as listOfValues
FROM [Form].[Fields] F
INNER JOIN [Form].FieldListValues FLV ON FLV.FieldID = F.FieldID
INNER JOIN [Item].[ItemDetailFieldListRecords] FLR ON FLV.FieldListValueID = FLR.FieldListValueID
WHERE FLR.ItemDetailID = @ItemDetailID
------------------
(4 row(s) affected)
FieldID ReferenceName CSV
-----------
4 test.DropDown 5, 8, 9, 10,
6 test.ListBox 5, 8, 9, 10,
6 test.ListBox 5, 8, 9, 10,
6 test.ListBox 5, 8, 9, 10,
=======================
A bit more tweaking to do. The original results were:
FieldID ReferenceName Value
----------
1 test.Textbox test textbox
2 test.TextArea Test TExt Area
4 test.DropDown 5
5 test.Cascading 6
6 test.ListBox 10
6 test.ListBox 8
6 test.ListBox 9
7 test.Checkbox True
9 test.Date 01/07/2007
10 test.Time 12:00 AM
==========
I can probably work on it from here. Thanks so much.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-04 : 14:34:23

A DISTINCT keyword maybe?

Peter Larsson
Helsingborg, Sweden

rternier
Starting Member

44 Posts

Posted - 2007-07-04 : 16:51:55

Aye, I threw that in there which cleaned it up nicely. I also employed:

SELECT LEFT(MyCsvList,LEN(MyCsvList)-1)
FROM (SELECT
(
SELECT col + ', ' as [text()]
FROM @t
ORDER BY col DESC
FOR XML PATH('')
) AS MyCsvList) z

which was from a comment in the above link.

Thanks for the help guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources