Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Distinct Select


Distinct Select

Author
Message
thava
thava
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 556
hi guys,
here i have a little need

DECLARE @project AS TABLE (Projectid INT, ProjectName VARCHAR(100))
DECLARE @Manager AS TABLE (Projectid INT, Managerid VARCHAR(100))
DECLARE @Developer AS TABLE (Projectid INT, Developerid VARCHAR(100))
DECLARE @User AS TABLE (Userid INT ,UserName VARCHAR(100))

INSERT INTO @project
SELECT 1,'Test'

INSERT INTO @User
SELECT dp.principal_id, dp.name FROM sys.database_principals dp WHERE dp.[type] ='s'

INSERT INTO @Manager
VALUES (1,1),(1,2)


INSERT INTO @Developer
VALUES (1,1),(1,3),(1,4)


SELECT p.ProjectName as PName,man.UserName AS Manager,dev.UserName AS Developer FROM @project p
INNER JOIN @Manager m ON m.Projectid = p.Projectid
INNER JOIN @Developer d ON d.Projectid = p.Projectid
INNER JOIN @User dev ON d.Developerid = dev.Userid
INNER JOIN @User man ON m.Managerid = man.Userid




The result is

PName Manager Developer
Test dbo dbo
Test dbo INFORMATION_SCHEMA
Test dbo sys
Test guest dbo
Test guest INFORMATION_SCHEMA
Test guest sys


but expected is

PName Manager Developer
Test dbo dbo
guest INFORMATION_SCHEMA
sys


with a minimal scan count and read count



Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9011 Visits: 19028
SELECT 
p.ProjectName as PName,
man.UserName AS Manager,
dev.UserName AS Developer
FROM @project p
CROSS APPLY (
SELECT STUFF( (
SELECT ', ' + man.UserName
FROM @Manager m
INNER JOIN @User man
ON m.Managerid = man.Userid
WHERE m.Projectid = p.Projectid
ORDER BY man.Userid
FOR XML PATH(''), TYPE).value('.', 'varchar(8000)')
,1,1,'')
) man (UserName)
INNER JOIN @Developer d
ON d.Projectid = p.Projectid
INNER JOIN @User dev
ON d.Developerid = dev.Userid



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
thava
thava
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 556
actually in our Db it is look like this, i change my code for a single project but actually it is for multiple project

DECLARE @res AS TABLE (Id INT, PName VARCHAR(100),Manager VARCHAR(100), Developer VARCHAR(100))
INSERT INTO @res (id,PName,Developer)
SELECT ROW_NUMBER()OVER (order by dev.Userid) AS id,p.ProjectName AS PName ,dev.UserName AS Developer
FROM @project p
INNER JOIN @Developer d ON d.Projectid = p.Projectid
INNER JOIN @User dev ON d.Developerid = dev.Userid

SELECT r.PName,x.Manager,r.Developer FROM @res r Left JOIN
(
SELECT ROW_NUMBER()OVER (order by man.Userid) AS id,man.UserName AS Manager
FROM @project p
INNER JOIN @Manager m ON m.Projectid = p.Projectid
INNER JOIN @User man ON m.Managerid = man.Userid
)x ON x.id = r.Id



i try to change this procedure to avoid that insert statement and an unwanted scan of project and User tables, any other idea, the result should not change, it look like as it is



Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9011 Visits: 19028
If your sample data doesn't look anything like your real data, then you are likely to receive queries which don't work when run against your real data.
How about an update on that sample data, so that it's a little more representative of what you've really got?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
HanShi
HanShi
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 3633
thava (8/23/2013)
The result is

PName Manager Developer
Test dbo dbo
Test dbo INFORMATION_SCHEMA
Test dbo sys
Test guest dbo
Test guest INFORMATION_SCHEMA
Test guest sys


but expected is

PName Manager Developer
Test dbo dbo
guest INFORMATION_SCHEMA
sys


with a minimal scan count and read count


If you want the result as expected I think you need to build a SSRS report. In a report you can define if you want to aggregate duplicate values in a row. A resultset in SQL cannot be aggregated like that and will show all values for each row.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9011 Visits: 19028
HanShi (8/23/2013)
... A resultset in SQL cannot be aggregated like that and will show all values for each row.


I wouldn't want to bet on that "cannot", HanShi. Perhaps "cannot easily" :-D

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
thava
thava
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 556

DECLARE @project AS TABLE (Projectid INT, ProjectName VARCHAR(100))
DECLARE @Manager AS TABLE (Projectid INT, Managerid VARCHAR(100))
DECLARE @Developer AS TABLE (Projectid INT, Developerid VARCHAR(100))
DECLARE @User AS TABLE (Userid INT ,UserName VARCHAR(100))

INSERT INTO @project
values(1,'Impact'),(2,'Process'),(3,'Tester')

INSERT INTO @User
SELECT c.column_id, c.name FROM master.sys.[columns] c WHERE c.[object_id] =OBJECT_ID('master.dbo.spt_monitor')

INSERT INTO @Manager
VALUES (1,1),(1,2),(2,1),(3,1),(3,5),(3,4)


INSERT INTO @Developer
VALUES (1,1),(1,3),(1,4),
(2,1),(2,3),(2,5),(2,6),(2,10),(2,8),
(3,3),(3,6),(3,7),(3,8),(3,9),(3,10)


Expected result would be like this

PName Manager Developer
Tester lastrun total_write
Tester idle io_busy
Tester pack_received pack_sent
Tester NULL connections
Tester NULL pack_errors
Tester NULL total_read
Process lastrun lastrun
Process NULL total_write
Process NULL io_busy
Process NULL pack_received
Process NULL pack_sent
Process NULL pack_errors
Impact lastrun lastrun
Impact cpu_busy io_busy
Impact NULL idle





Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script
thava
thava
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 556
Hi HanShi,
thanks for your advice, but the problem is we are not using the SSRS and More over we create a report for our purpose so there is no Such option for that any help is use full to me



Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script
HanShi
HanShi
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 3633
ChrisM@Work (8/23/2013)

I wouldn't want to bet on that "cannot", HanShi. Perhaps "cannot easily" :-D


You are absolutely right, but I wanted to prevent a possible "Aah, so it can be done! Please give me a sample" reaction. ;-)

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
thava
thava
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 556
well i got it even a better result


;WITH Users AS(
SELECT 'D' as UserType,ROW_NUMBER() OVER ( PARTITION BY d.Projectid ORDER BY d.Developerid) AS Rid, d.Developerid AS USerid,d.Projectid FROM @Developer d
UNION ALL
SELECT 'M' as UserType,ROW_NUMBER() OVER ( PARTITION BY d.Projectid ORDER BY d.Managerid), d.Managerid,d.Projectid FROM @Manager d
)
SELECT CASE WHEN u.Rid =1 THEN p.ProjectName ELSE NULL END AS Pname, Min(CASE WHEN u.Usertype='M' THEN usr.UserName ELSE NULL END) AS Manager,
Min(CASE WHEN u.Usertype='D' THEN usr.UserName ELSE NULL END) AS Developer
FROM @project p
INNER JOIN Users U ON U.Projectid = p.Projectid
INNER JOIN @User Usr ON u.userid = usr.Userid
GROUP BY p.ProjectName, u.Rid
ORDER BY p.ProjectName DESC





Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search