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.
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
Éric Moreau
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
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
Never mind. This is a duplicate question and should be deleted. Comments should be posted in the original question here:
https://www.experts-exchange.com/questions/27993594/SQL-Query-Help.html
https://www.experts-exchange.com/questions/27993594/SQL-Query-Help.html
Zolf ,
Can you post here what is required output.... ?
Thanks,
saurabh
Can you post here what is required output.... ?
Thanks,
saurabh
ASKER
Saurv
please see the screenshot i have attached in the main post
acperkins
no it did not give me the required result
please see the screenshot i have attached in the main post
acperkins
no it did not give me the required result
ASKER
that link provided by: acperkins is exactly what i am trying to do.but i dont know what is wong in my query
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks