Solved

Complex SQL Query

Posted on 2013-01-12
10
301 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
10 Comments
 
LVL 69

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
 
LVL 12

Expert Comment

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

Thanks,
saurabh
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Error in WHERE Clause 5 41
Need to update TableA to TableB 6 34
Generate Weekly Schedule 15 16
T-SQL: Nested CASE Statements 4 24
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now