October 3, 2014 at 8:23 am
I've got below data (Sample table)
NameServerSpeed
SITE1SERVERA100
SITE1SERVERB100
SITE2SERVERA100
SITE2SERVERB100
SITE2SERVERC10
SITE3SERVERA10
SITE3SERVERB100
I have 2 questions about this
- I would like to return only 1 row/site but it should return server with lowest speed, if there are multiple servers with same speed, should only show 1 server.
Example
SITE1 SERVERA
SITE2 SERVERC
SITE3 SERVERA
- I would like to return only 1 row/site but have all servers listed in 1 column separated by "|" or any other separator for that matter
Example
SITE1 SERVERA|SERVERB
SITE2 SERVERA|SERVERB|SERVERC
SITE3 SERVERA|SERVERB
here is the DDL Statement to create table and populate with dummy data
I've got this DDL statement to help with creating table and some dummy data
CREATE TABLE [dbo].[custSiteInfo](
[Name] [nvarchar](255) NULL,
[Server] [nvarchar](255) NULL,
[Speed] int
) ON [PRIMARY]
GO
INSERT INTO [dbo].[custSiteInfo] ([Name],[Server] ,[Speed]) VALUES ('SITE1','SERVERA',1)
INSERT INTO [dbo].[custSiteInfo] ([Name],[Server] ,[Speed]) VALUES ('SITE1','SERVERB',1)
INSERT INTO [dbo].[custSiteInfo] ([Name],[Server] ,[Speed]) VALUES ('SITE2','SERVERA',1)
INSERT INTO [dbo].[custSiteInfo] ([Name],[Server] ,[Speed]) VALUES ('SITE2','SERVERB',1)
INSERT INTO [dbo].[custSiteInfo] ([Name],[Server] ,[Speed]) VALUES ('SITE2','SERVERC',0)
INSERT INTO [dbo].[custSiteInfo] ([Name],[Server] ,[Speed]) VALUES ('SITE3','SERVERA',0)
INSERT INTO [dbo].[custSiteInfo] ([Name],[Server] ,[Speed]) VALUES ('SITE3','SERVERB',1)
GO
Thx.
October 3, 2014 at 8:38 am
How about something like
WITH cte AS (
select [name],
[server],
[speed],
ROW_NUMBER () OVER(PARTITION by [NAME] order by [speed]) AS cnt
from #custSiteInfo)
select * from cte
WHERE cnt = 1;
October 3, 2014 at 9:50 am
The first query is just a reiteration of what djj just posted. The idea is that you use the row_number window function to partition, or group, the data by name, and the order it by server speed. You end up with an incrementing number over each group (or partition) of names ordered from the lowest speed to the highest, Then you select out the one with a value of 1, knowing it will correspond to the rows with the lowest speed per server.
select *
from (select
RID = row_number() over(partition by Name order by Speed),
Name,
[Server],
Speed
from #CustSiteInfo) a
where a.RID = 1
The second query uses an xml function to concatenate row values into a single column. Starting from the inner-most section, you concatenate all the servers with a pipe character, and using a correlated subquery (i.e. joining the contents of that xml query to the outer table aliased as "a") tie each row to the correct name.
In the next higher level where I've got name and that big xml subquery being returned, I've grouped it by Name. If you don't you'll have duplicate rows (as many as you have servers for a given site name). Finally, the reason I put that in another subquery was simply to chop off the trailing pipe. There are other ways to do this, but this is my preferred way. Let me know if you have any questions on what's going on here.
select
Name,
ServerList = left(ServerList, len(ServerList) - 1)
from (select
a.Name,
ServerList = (select b.[Server] + '|'
from #CustSiteInfo b
where a.Name = b.Name
for xml path (''))
from #CustSiteInfo a
group by a.Name) z
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply