Help with Query.

Friends,
I have a table as shown below,
ABC DEF
23 2156
34 2156
41 2156
34 2157
38 2157
41 2157
I would like to return data for ABC column in a comma seperated format, I
would like to import the data into a new table.
23,34,41 2156
34,38,41 2157
How can i do that?
Any help is greatly appreciated.
TIA,
Santosh
Santhosh,
Here is an example. modify it to fit your schema.
CREATE TABLE Users(Uid int, Username VARCHAR(35))
CREATE TABLE Roles(Rid int, RoleName VARCHAR(35))
CREATE TABLE UserRoles(Uid int, Rid int)
Go
INSERT INTO Users VALUES(1, 'A')
INSERT INTO Users VALUES(2, 'B')
INSERT INTO Users VALUES(3, 'C')
INSERT INTO Users VALUES(4, 'D')
INSERT INTO Roles Values(1,'Admin')
INSERT INTO Roles Values(2,'Accounts')
INSERT INTO Roles Values(3,'Operations')
INSERT INTO Roles Values(4,'Marketing')
INSERT INTO UserRoles VALUES(1,1)
INSERT INTO UserRoles VALUES(1,4)
INSERT INTO UserRoles VALUES(2,3)
INSERT INTO UserRoles VALUES(2,4)
INSERT INTO UserRoles VALUES(3,1)
INSERT INTO UserRoles VALUES(3,2)
INSERT INTO UserRoles VALUES(3,3)
INSERT INTO UserRoles VALUES(4,2)
Go
CREATE Function dbo.GetRoles(@.Uid int)
RETURNS VARCHAR(400)
AS
BEGIN
DECLARE @.vchRoleList VARCHAR(400)
SET @.vchRoleList = ''
SELECT @.vchRoleList = @.vchRoleList +
CASE WHEN @.vchRoleList= '' THEN '' ELSE ', ' END + RoleName
FROM Roles R
INNER JOIN UserRoles UR
ON R.Rid = UR.Rid AND UR.UId = @.Uid
RETURN @.vchRoleList
END
GO
SELECT U.Uid, U.UserName, dbo.GetRoles(Uid)
FROM Users U
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
news:eIBVj844EHA.3368@.TK2MSFTNGP10.phx.gbl...
> Friends,
> I have a table as shown below,
> ABC DEF
> --
> 23 2156
> 34 2156
> 41 2156
> 34 2157
> 38 2157
> 41 2157
>
> I would like to return data for ABC column in a comma seperated format, I
> would like to import the data into a new table.
> 23,34,41 2156
> 34,38,41 2157
> How can i do that?
> Any help is greatly appreciated.
> TIA,
> --
> Santosh
>
>
|||It works.
Thanks,
Appreciate it.
Santosh
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:eD8U7354EHA.2624@.TK2MSFTNGP11.phx.gbl...
> Santhosh,
> Here is an example. modify it to fit your schema.
>
> CREATE TABLE Users(Uid int, Username VARCHAR(35))
> CREATE TABLE Roles(Rid int, RoleName VARCHAR(35))
> CREATE TABLE UserRoles(Uid int, Rid int)
> Go
> INSERT INTO Users VALUES(1, 'A')
> INSERT INTO Users VALUES(2, 'B')
> INSERT INTO Users VALUES(3, 'C')
> INSERT INTO Users VALUES(4, 'D')
> INSERT INTO Roles Values(1,'Admin')
> INSERT INTO Roles Values(2,'Accounts')
> INSERT INTO Roles Values(3,'Operations')
> INSERT INTO Roles Values(4,'Marketing')
>
> INSERT INTO UserRoles VALUES(1,1)
> INSERT INTO UserRoles VALUES(1,4)
> INSERT INTO UserRoles VALUES(2,3)
> INSERT INTO UserRoles VALUES(2,4)
> INSERT INTO UserRoles VALUES(3,1)
> INSERT INTO UserRoles VALUES(3,2)
> INSERT INTO UserRoles VALUES(3,3)
> INSERT INTO UserRoles VALUES(4,2)
> Go
> CREATE Function dbo.GetRoles(@.Uid int)
> RETURNS VARCHAR(400)
> AS
> BEGIN
> DECLARE @.vchRoleList VARCHAR(400)
> SET @.vchRoleList = ''
> SELECT @.vchRoleList = @.vchRoleList +
> CASE WHEN @.vchRoleList= '' THEN '' ELSE ', ' END + RoleName
> FROM Roles R
> INNER JOIN UserRoles UR
> ON R.Rid = UR.Rid AND UR.UId = @.Uid
> RETURN @.vchRoleList
> END
> GO
> SELECT U.Uid, U.UserName, dbo.GetRoles(Uid)
> FROM Users U
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
> news:eIBVj844EHA.3368@.TK2MSFTNGP10.phx.gbl...
>