Selecting all the row values together
-
I have a requirement where I have a table like
ID Value
1 a
1 b
2 x
2 y
2 z
3 m
4 q
4 r
4 s
4 t
and I need to do a SELECT such that I get
id value_string (All values sprtd by comma)
1 a,b
2 x,y,z
3 m
4 q,r,s,t,u
-
IF Object_id('ListTableColumns') > 0
DROP FUNCTION ListTableColumns
GO
CREATE FUNCTION dbo.ListTableColumns (@TableID as int)
RETURNS varchar(8000)
AS
BEGIN
Declare @Items as varchar(8000)
SET @Items = ''
SELECT
@Items = @Items + C.Name + ', '
FROMdbo.SysColumns C
WHEREC.id = @TableID
AND OBJECTPROPERTY(@TableID, 'IsTable') = 1
ORDER BYC.Name
SET @Items = LEFT(@Items, ABS(DATALENGTH(@Items) - 2))
RETURN @Items
END
GO
Select dbo.ListTableColumns(Object_id('SysObjects'))
--base_schema_ver, cache, category, crdate, deltrig, ftcatid, id, indexdel, info, instrig, name, parent_obj, refdate, replinfo, schema_ver, seltrig, stats_schema_ver, status, sysstat, type, uid, updtrig, userstat, version, xtype
DROP FUNCTION ListTableColumns
-
easier than trimming at the end:
Dont initialize @items
and
SELECT @items = ISNULL( @items + ',', '' ) + column...
-
This sure is a good way Thanks -
My problem here is a little different. I have a table (say TABLE1) carrying two columns ID and VALUE.
An ID can have multiple values - as shown in my earlier posting. I need to select each unique ID along with all the VALUEs separated by comma from the table TABLE1. As shown in the previuos posting.
May be I can use your solution with some tweaks - or if you have some more suggestions.
-
Select id, dbo.fnName(id) from dbo.YourTable????????
-
So, it becomes sort of a cursor logic where I will be calling the function for each distinct ID. Can it be done in some set based manner - or that is not possible?
-
Any subquery is actually "sort of cursor logic".
But here you do group by TableId first, so it's mix of "cursor" and "set" logic.
Nothing to worry about.
_____________
Code for TallyGenerator -
SELECT CONVERT( varchar, SomeDate, 101) FROM SomeTable. This uses a function as does Remi's [RGR'us] and it is SET BASED.
Think of this solution in the same manner... (ssshhhh..., don't mention Cursor around Remi...
) I wasn't born stupid - I had to study.
-
Around almost anyone with more than 250 post actually
. -
ROFLOL
(I will note that for future reference...)
I wasn't born stupid - I had to study.
-
Thanks to all you guys.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply