Distinct Select
-
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
PNameManagerDeveloper
Testdbodbo
TestdboINFORMATION_SCHEMA
Testdbosys
Testguestdbo
TestguestINFORMATION_SCHEMA
Testguestsys
but expected is
PNameManagerDeveloper
Testdbodbo
guestINFORMATION_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 [/url] -
SELECTp.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 ShawFor 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 -
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 [/url] -
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 ShawFor 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 -
thava (8/23/2013)
The result isPNameManagerDeveloper
Testdbodbo
TestdboINFORMATION_SCHEMA
Testdbosys
Testguestdbo
TestguestINFORMATION_SCHEMA
Testguestsys
but expected is
PNameManagerDeveloper
Testdbodbo
guestINFORMATION_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’! ** -
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 ShawFor 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 -
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
PNameManagerDeveloper
Testerlastruntotal_write
Testeridleio_busy
Testerpack_receivedpack_sent
TesterNULLconnections
TesterNULLpack_errors
TesterNULLtotal_read
Processlastrunlastrun
ProcessNULLtotal_write
ProcessNULLio_busy
ProcessNULLpack_received
ProcessNULLpack_sent
ProcessNULLpack_errors
Impactlastrunlastrun
Impactcpu_busyio_busy
ImpactNULLidle
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 [/url] -
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 [/url] -
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’! ** -
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 [/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply