SQL select statement issue - 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)

SQL select statement issue

Author

Topic

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2010-06-02 : 00:40:40

I have the following sql:

Select Distinct(AssignedPrivileges) From ROLES_FORMS Where Form_Id=17 AND Role_Id IN(Select Role_Id From Users_Roles Where [USER_ID]='1')

The result I get will be two rows:

1) AD
2) AVU

How can I change my sql so that it will return only one row of result with distinct string such as "AVUD" rather than duplicate character??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-02 : 03:39:58

[code]DECLARE @Sample TABLE
(
PersonID INT NOT NULL,
Rights VARCHAR(8) NOT NULL
)

INSERT @Sample
VALUES (1, 'AD'),
(1, 'AVUD')

SELECT CAST(Data AS VARCHAR(8)) AS CombinedRights
FROM (
SELECT DISTINCT SUBSTRING(s.Rights, v.Number, 1)
FROM @Sample AS s
INNER JOIN master..spt_values AS v ON v.Type = 'P'
AND v.Number BETWEEN 1 AND DATALENGTH(s.Rights)
WHERE s.PersonID = 1
FOR XML PATH('')
) AS d(Data)[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources