Solved

Complex SQL Query

Posted on 2013-01-12
10
303 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 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS 2012 r2 - Parm Drop Down has Date/Time 12 31
sql server computed columns 11 30
T-SQL: Do I need CLUSTERED here? 13 41
Find results from sql within a time span 11 29
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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
Viewers will learn how the fundamental information of how to create a table.

776 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