QUERY - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

QUERY

Author

Topic

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-05-26 : 13:49:59

I HAVE SOME RECORDS IN TABLE A:

EMPLOYEEID ROLESID
1 2
2 1,2
3 5
4 2,4,6,

THIS ROLESID IS COMING FROM MASTER TABLE
WHICH HAS

ROLEID ROLE
1 A
2 B
3 C
4 D
5 E
6 F

NOW I WANT THE RESULT LIKE BELOW:

EMPLOYEEID ROLE

1 B
2 A,B
3 E
4 B,D,F

PLS HELP ME TO GET THIS IN QUERY

THX IN ADVANCE

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-05-30 : 15:47:47

Do you have a solution yet?

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-05-31 : 03:01:18

I got the solution

and it works perfect now

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-31 : 03:48:59

quote:
Originally posted by Arun.G

I got the solution

and it works perfect now

Post the solution that would help others

Madhivanan

Failing to plan is Planning to fail

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-06-01 : 08:43:37

using the following function:

ALTER FUNCTION [dbo].[SplitCSV] (@CSVString VARCHAR(8000), @Delimiter CHAR(1))
RETURNS @temptable TABLE (items VARCHAR(8000))
AS
BEGIN
DECLARE @pos INT;
DECLARE @slice VARCHAR(8000);
SELECT @pos = 1;
IF LEN(@CSVString) < 1 OR @CSVString IS NULL RETURN;
WHILE @pos!= 0
BEGIN
SET @pos = CHARINDEX(@Delimiter,@CSVString);
IF @pos != 0
SET @slice = LEFT(@CSVString, @pos - 1);
ELSE
SET @slice = @CSVString;
IF( LEN(@slice) > 0)
INSERT INTO @temptable(Items) VALUES (@slice);
SET @CSVString = RIGHT(@CSVString, LEN(@CSVString) - @pos);
IF LEN(@CSVString) = 0 BREAK;
END
RETURN
END

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-06-01 : 15:48:26

It looks like you are using function to split the value (2,4,5) into a table .... sound good. But what is going on after that? Could you go more detail?

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-06-02 : 01:24:15

I used that spilit function in my stored procedure to display multiple roles:

create PROCEDURE <procedure name>
@parameter integer

AS

DECLARE @LISTSTR1 Varchar(1000)
SELECT @LISTSTR1 = <columnname> from <tablename> where <condition>
DECLARE @liststr varchar(8000)
SELECT @liststr = COALESCE(@liststr + ', ','') + dbo.FN_ID_TO_NAME(CAST(items AS INT)) FROM dbo.SplitCSV (@LISTSTR1, ',');

SELECT
column1,
@liststr AS ROLES,
column3

FROM <tabl.name> WHERE <condition>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources