Solved

Complex SQL Query

Posted on 2013-01-12
10
309 Views
Last Modified: 2013-01-18
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
Comment
Question by:zolf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 38772045
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38772706
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38773035
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38773287
Zolf ,
Can you post here what is required output.... ?

Thanks,
saurabh
0
 

Author Comment

by:zolf
ID: 38773357
Saurv

please see the screenshot i have attached in the main post

acperkins

no it did not give me the required result
0
 

Author Comment

by:zolf
ID: 38773364
that link provided by: acperkins is exactly what i am trying to do.but i dont know what is wong in my query
0
 

Author Comment

by:zolf
ID: 38773381
i have added the screenshot with more description to my problem
q7.gif
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 38785488
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
 

Author Closing Comment

by:zolf
ID: 38795674
thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question