Solved

Complex SQL Query

Posted on 2013-01-12
10
300 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Zolf ,
Can you post here what is required output.... ?

Thanks,
saurabh
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:zolf
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
thanks
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In this article I will describe the Copy Database Wizard 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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

11 Experts available now in Live!

Get 1:1 Help Now