Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Distinct Select Expand / Collapse
Author
Message
Posted Friday, August 23, 2013 4:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 12:30 AM
Points: 253, Visits: 540
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
Post #1487718
Posted Friday, August 23, 2013 4:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:43 AM
Points: 6,890, Visits: 14,254
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
Post #1487733
Posted Friday, August 23, 2013 5:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 12:30 AM
Points: 253, Visits: 540
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
Post #1487742
Posted Friday, August 23, 2013 5:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:43 AM
Points: 6,890, Visits: 14,254
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
Post #1487748
Posted Friday, August 23, 2013 5:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:40 AM
Points: 2,480, Visits: 3,023
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’! **
Post #1487762
Posted Friday, August 23, 2013 5:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:43 AM
Points: 6,890, Visits: 14,254
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"


“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
Post #1487766
Posted Friday, August 23, 2013 5:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 12:30 AM
Points: 253, Visits: 540
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
Post #1487768
Posted Friday, August 23, 2013 5:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 12:30 AM
Points: 253, Visits: 540
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
Post #1487774
Posted Friday, August 23, 2013 6:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:40 AM
Points: 2,480, Visits: 3,023
ChrisM@Work (8/23/2013)

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


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’! **
Post #1487780
Posted Tuesday, August 27, 2013 6:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 12:30 AM
Points: 253, Visits: 540
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
Post #1488698
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse