Reputation: 1931

SQL - Filtering the Duplicate

I have a sql Query working fine but not able to generate accurate result

My Query Details as follows :

declare @test varchar(500)
 SELECT @test=coalesce(@test+',','') + cast(RoleName as varchar) FROM                                                               
        (   select roles.RoleName  from LU_BCOMS_usersroles usrroles
            inner join LU_BCOMS_roles roles 
            on roles.roleid     =   usrroles.Roles_roleid 
            where Users_Userid='MV10310'
        ) as Tbl 

select repfamily.ProductName as Category,repfamily.Family as SeqChange,repfamily.RepFamily as DescOfChange, req.*,
 TaskCompVer =
 CASE WHEN req.UpdateByASPM is not null THEN  'Provide PLQM Wish List'
        WHEN req.UpdateByASPM  is null THEN 'Provide ASPM Wish List'
        WHEN req.CreatedBy is not null THEN  'Provide ASPM Wish List'
        END
  from performa.TX_BCOMS_Request as req   

        inner join TX_BCOMS_Requestrepfamily family on 
        family.request_requestid=req.requestid
        inner  join LU_BCOMS_RepFamily as repfamily on 
        family.RepFamily_repFamilyid=repfamily.repfamilyid         

  where req.UpdatedByPLQM is null  and (

   ((CHARINDEX('ASPM',@test)> 0 and CHARINDEX('PLQM',@test)> 0)  and  req.UpdatedByPLQM IS null)
   or 
   ((CHARINDEX('PLQM' ,@test)> 0)  and req.UpdateByASPM IS NOT null)
   or 
   ((CHARINDEX('ASPM',@test)> 0 )  and  req.UpdateByASPM IS null)
   or 
   ((CHARINDEX('PLQM' ,@test)> 0)  and req.UpdateByASPM IS NOT null)
   or 
   ((CHARINDEX('ASPM' ,@test)< 0 and CHARINDEX('PLQM',@test) < 0)  and req.CreatedBy IS null)
   )

Output :

Caterogy  SeqCategory   DescofChange    RequestId     TaskCompVer

BIGBEAR    BIGBEAR  BIGBEAR     B14020002    Provide ASPM Wish List
ARCUS3PL   KOJN-RE  ARCUS3PL        B14020002    Provide ASPM Wish List
AURORA     Aurora   Aurora          B14020003    Provide ASPM Wish List

When requestId and TaskCompVer are same there is no need to show 2 records, have to filter something like below..

I need output like below :

Output : 

    Caterogy            SeqCategory        DescofChange    RequestId TaskCompVer

    BIGBEAR,ARCUS3PL   BIGBEAR,KOJN-RE  BIGBEAR,ARCUS3PL   B14020002 Provide ASPM Wish List
    AURORA             Aurora            Aurora            B14020003 Provide ASPM Wish List

I need to display the actual as above I tried using STUFF function cannot able to generate the actual output...

Upvotes: 0

Answers (3)

Siddique Mahsud

Reputation: 1453

May it helpful for you.

CREATE TABLE tempTable(name VARCHAR(50),subjects VARCHAR(50),phone VARCHAR(50))

INSERT INTO tempTable VALUES
('siddique','CRM','123456'),('siddique','Asp.net','9874563'),
('siddique','sql server','44451685'),('Danish','MVC','123456'),
('Danish','sql server','9874563'),('Danish','WCF','44451685'),
('shah g','Account','123456'),('shah g','MBA','9874563'),
('shah g','Math','44451685')

Your simple query select all data

SELECT * FROM tempTable 

name        subjects      phone
siddique    CRM           123456
siddique    Asp.net       9874563
siddique    sql server    44451685
Danish      MVC           123456
Danish      sql server    9874563
Danish      WCF           44451685
shah g      Account       123456
shah g      MBA           9874563
shah g      Math          44451685

Using STUFF to comma seperate your values agaist each name (GROUP BY name)

SELECT 
name
,STUFF((SELECT ', ' + subjects 
FROM tempTable temp2 WHERE temp2.name=temp1.name              
FOR XML PATH('')), 1, 1, '') AS subjects

,STUFF((SELECT '; ' + phone 
FROM tempTable temp2 WHERE temp2.name=temp1.name              
FOR XML PATH('')), 1, 1, '') AS phones

FROM tempTable temp1
GROUP BY name

DROP TABLE tempTable

Output:

name       subjects                  phones
Danish     MVC, sql server, WCF      123456; 9874563; 44451685
shah g     Account, MBA, Math        123456; 9874563; 44451685
siddique   CRM, Asp.net, sql server  123456; 9874563; 44451685

Upvotes: 1

sureshhh

Reputation: 1216

try this,,,,

declare @test varchar(500)
 SELECT @test=coalesce(@test+',','') + cast(RoleName as varchar) FROM                                                               
        (   select roles.RoleName  from LU_BCOMS_usersroles usrroles
            inner join LU_BCOMS_roles roles 
            on roles.roleid     =   usrroles.Roles_roleid 
            where Users_Userid='MV10310'
        ) as Tbl 

select
       req.*,
 TaskCompVer =
 CASE WHEN req.UpdateByASPM is not null THEN  'Provide PLQM Wish List'
        WHEN req.UpdateByASPM  is null THEN 'Provide ASPM Wish List'
        WHEN req.CreatedBy is not null THEN  'Provide ASPM Wish List'
        END,
       STUFF(
             ( 
                select ','+repfamily.ProductName 
                from TX_BCOMS_Requestrepfamily  family              
                    inner  join LU_BCOMS_RepFamily as repfamily     on family.RepFamily_repFamilyid=repfamily.repfamilyid                   
                where family.request_requestid=req.requestid

                FOR XML PATH('') ), 1, 1, '' ) as 'Category',
       STUFF(
             ( 
                select ','+repfamily.Family 
                from TX_BCOMS_Requestrepfamily  family              
                    inner  join LU_BCOMS_RepFamily as repfamily     on family.RepFamily_repFamilyid=repfamily.repfamilyid                   
                where family.request_requestid=req.requestid

                FOR XML PATH('') ), 1, 1, '' ) as 'SeqChange',
       STUFF(
             ( 
                select ','+repfamily.RepFamily 
                from TX_BCOMS_Requestrepfamily  family              
                    inner  join LU_BCOMS_RepFamily as repfamily     on family.RepFamily_repFamilyid=repfamily.repfamilyid                   
                where family.request_requestid=req.requestid

                FOR XML PATH('') ), 1, 1, '' ) as 'DescOfChange' ,

 repfamily.ProductName as Category,repfamily.Family as SeqChange,repfamily.RepFamily as DescOfChange,
  from performa.TX_BCOMS_Request as req         

  where req.UpdatedByPLQM is null  and (

   ((CHARINDEX('ASPM',@test)> 0 and CHARINDEX('PLQM',@test)> 0)  and  req.UpdatedByPLQM IS null)
   or 
   ((CHARINDEX('PLQM' ,@test)> 0)  and req.UpdateByASPM IS NOT null)
   or 
   ((CHARINDEX('ASPM',@test)> 0 )  and  req.UpdateByASPM IS null)
   or 
   ((CHARINDEX('PLQM' ,@test)> 0)  and req.UpdateByASPM IS NOT null)
   or 
   ((CHARINDEX('ASPM' ,@test)< 0 and CHARINDEX('PLQM',@test) < 0)  and req.CreatedBy IS null)
   )

Upvotes: 0

user3223048

Reputation: 163

I needed a similar Query where i needed it the same way... Here is My Query:

SELECT  
'All Users' as QuestionOption,
Stuff( (SELECT N'; ' + email FROM users where email>='  ' FOR XML PATH(''),TYPE) 
.value('text()[1]','nvarchar(max)'),1,2,N'') as QuestionOptionValue
UNION
SELECT  'All Volunteers' as QuestionOption, Stuff( (SELECT N'; ' + U.email FROM 
dbo.Users  AS U LEFT OUTER JOIN (SELECT up.UserID, MAX(CASE WHEN ppd.PropertyName = \
'Volunteer' THEN up.PropertyValue ELSE '' END) AS Volunteer
FROM 
dbo.UserProfile AS up 
INNER JOIN dbo.ProfilePropertyDefinition AS ppd ON 
up.PropertyDefinitionID = ppd.PropertyDefinitionID and ppd.PortalID = 0 Group By 
up.UserID) as upd on U.UserID = upd.UserID Where upd.Volunteer='True'  FOR XML 
PATH(''),TYPE)  .value('text()[1]','nvarchar(max)'),1,2,N'') as QuestionOptionValue 
UNION
SELECT  'All Committees' as QuestionOption, Stuff( (SELECT N'; ' + U.email FROM 
dbo.USERS AS U LEFT OUTER JOIN (SELECT up.UserID, MAX(CASE WHEN ppd.PropertyName = 
'Committee' THEN up.PropertyValue ELSE '' END) AS Committee FROM dbo.UserProfile AS 
up INNER JOIN dbo.ProfilePropertyDefinition AS ppd ON up.PropertyDefinitionID = 
ppd.PropertyDefinitionID and ppd.PortalID = 0 Group By up.UserID) as upd on U.UserID 
= upd.UserID Where upd.Committee >' '  FOR XML PATH(''),TYPE) .value('text()
[1]','nvarchar(max)'),1,2,N'') as QuestionOptionValue

Im not the best at writing them, so you could use mine as an example. my output is:

QuestionOption                      QuestionOptionValue

All Committees                       [email protected]; [email protected]
All Users                            [email protected]; [email protected]
All Volunteers                       [email protected]; [email protected]

I hope this helps you!

Upvotes: 0

SQL - Filtering the Duplicate

Answers (3)

Related Questions