• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

Complex SQL Query

Hello there,

I have a query which when i run needs to add comma between data something like this this user1,user2. the field whic is doing this work is this

STUFF((SELECT ',' + name FROM Branch b WHERE ru.branchid = b.id FOR XML PATH ('')), 1, 1, '') AS branchname1

the query looks like this but it is returning the cols in seperate rows instead of one row.



SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY x.id, usertypeid) AS rowID FROM (SELECT TOP 100 PERCENT   

 ruc.id,ruc.username,
 
ru.id AS reportuserpermissionid,ruc.password,

reportusertype.id AS usertypeid,

Section.id AS sectionid,

reportusertype.name AS usertypename,b.name AS branchname,Section.name AS sectionname,
 
STUFF((SELECT ',' + name FROM Branch b WHERE ru.branchid = b.id FOR XML PATH ('')), 1, 1, '') AS branchname1

		     FROM 
 
			   reportusercredential ruc, reportuserpermission ru,Section,Branch b,reportusertype

			 WHERE 

			    ru.reportuserid = ruc.id AND ru.sectionid=Section.id
 
				AND ru.branchid = b.id AND reportusertype.id = ruc.usertypeid AND (('1'='1'))

 			GROUP BY 

			   ruc.id,ruc.username,ru.id,ruc.password,reportusertype.id,Section.id,reportusertype.name,b.name,
 
			  Section.name,b.name,ru.branchid

			 ) x) y WHERE y.rowID BETWEEN 1 AND 75

Open in new window

q7.gif
0
zolf
Asked:
zolf
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
have you tried simplifying your query to a shorter version like the one in http://emoreau.com/Entries/Blogs/2009/10/SQL-Concatenate-values-multiple-from-multiple-lines.aspx
0
 
Anthony PerkinsCommented:
Perhaps if you did not JOIN against Branch in the outer query this would not happen.  As in:
SELECT  *
FROM    (SELECT *,
                ROW_NUMBER() OVER (ORDER BY x.id, usertypeid) AS rowID
         FROM   (SELECT TOP 100 PERCENT
                        ruc.id,
                        ruc.username,
                        ru.id AS reportuserpermissionid,
                        ruc.password,
                        rt.id AS usertypeid,
                        s.id AS sectionid,
                        rt.name AS usertypename,
                        s.name AS sectionname,
                        STUFF((SELECT   ',' + name
                               FROM     Branch b
                               WHERE    ru.branchid = b.id
                              FOR
                               XML PATH('')
                              ), 1, 1, '') AS branchname1
                 FROM   reportusercredential ruc
                        Inner Join reportuserpermission ru ON ru.reportuserid = ruc.id
                        INNER JOIN Section s ON ru.sectionid = s.id
                        INNER JOIN reportusertype rt ON rt.id = ruc.usertypeid
                 GROUP BY ruc.id,
                        ruc.username,
                        ru.id,
                        ruc.[password],
                        rt.id,
                        Section.id,
                        rt.name,
                        s.name,
                        ru.branchid
                ) x
        ) y
WHERE   y.rowID BETWEEN 1 AND 75

Open in new window

0
 
Anthony PerkinsCommented:
Never mind.  This is a duplicate question and should be deleted.  Comments should be posted in the original question here:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_27993594.html
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Saurabh BhadauriaCommented:
Zolf ,
Can you post here what is required output.... ?

Thanks,
saurabh
0
 
zolfAuthor Commented:
Saurv

please see the screenshot i have attached in the main post

acperkins

no it did not give me the required result
0
 
zolfAuthor Commented:
that link provided by: acperkins is exactly what i am trying to do.but i dont know what is wong in my query
0
 
zolfAuthor Commented:
i have added the screenshot with more description to my problem
q7.gif
0
 
Mark WillsTopic AdvisorCommented:
OK, if you want to concatenate branch names, you either have to do it after the grouping, or, do the grouping, but need to take branch out of that grouping...

Doing the second choice first and useing your original query have a look at :

SELECT * FROM 

(SELECT *, ROW_NUMBER() OVER (ORDER BY x.id, usertypeid) AS rowID 


FROM (SELECT --TOP 100 PERCENT   
             ruc.id,ruc.username,
             ru.id AS reportuserpermissionid,ruc.password,
             reportusertype.id AS usertypeid,
             Section.id AS sectionid,
             reportusertype.name AS usertypename,
-------             b.name AS branchname,
             Section.name AS sectionname,

             STUFF((SELECT ',' + name FROM Branch b inner join reportuserpermission ru1 on ru1.branchid = b.id WHERE ru1.reportuserid = ruc.id FOR XML PATH ('')), 1, 1, '') AS branchname1
       FROM  reportusercredential ruc
       INNER JOIN reportuserpermission ru on ru.reportuserid = ruc.id 
       INNER JOIN Section on ru.sectionid=Section.id 
-------       INNER JOIN Branch b on ru.branchid = b.id
       INNER JOIN reportusertype on reportusertype.id = ruc.usertypeid
       WHERE (('1'='1'))

       GROUP BY ruc.id,ruc.username,ru.id,ruc.password,reportusertype.id,Section.id,reportusertype.name, Section.name
 
-------              b.name,ru.branchid

             ) x) y WHERE y.rowID BETWEEN 1 AND 75

Open in new window


Or might be better to use a CTE query - in part have a look at :

;with cte_users as
(
      SELECT --TOP 100 PERCENT   
             ruc.id,ruc.username,
             ru.id AS reportuserpermissionid,ruc.password,
             reportusertype.id AS usertypeid,
             Section.id AS sectionid,
             reportusertype.name AS usertypename,
             b.name AS branchname,
             Section.name AS sectionname
       FROM  reportusercredential ruc
       INNER JOIN reportuserpermission ru on ru.reportuserid = ruc.id 
       INNER JOIN Section on ru.sectionid=Section.id 
       INNER JOIN Branch b on ru.branchid = b.id
       INNER JOIN reportusertype on reportusertype.id = ruc.usertypeid
       WHERE (('1'='1'))

)

select id,username,reportuserpermissionid, password, usertypeid, sectionid, usertypename, sectionname,
       STUFF((SELECT ',' + branchname FROM cte_users C WHERE C.id = U.id and c.reportuserpermissionid = U.reportuserpermissionid FOR XML PATH ('')), 1, 1, '') AS branchname1
from cte_users U
group by id,username,reportuserpermissionid, password, usertypeid, sectionid, usertypename, sectionname

Open in new window



Now, there is a small challenge and a few assumptions... Particularly the join with reportuserpermission and the use of naming convention of ID.

If there is one RU.ID per branch in a multi-user branch, then having RU.ID as part of the uniqueness might create undesired results. The thought is that ID would be unique per row in keeping with different branches, but, seems that there should be some kind of persmission group that multiple people might belong to (and that is the one that should be used in the grouping). So, might need to clarify that RU.ID key if the above doesnt achieve your desired results.
0
 
zolfAuthor Commented:
thanks
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now