Help with a query 1

[TheBugSlayer]

Hi guys,

I need to help a friend with a query and I must disclose that I have not have time to think about it myself since yesterday when he submitted it to me but I managed to create the table and put the data in it. So please only help if you have the time yourself...

CREATE TABLE [dbo].[SomeTable](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[IP] [nvarchar](50) NULL,
	[Role] [nchar](10) NULL,
	[Grp] [nchar](10) NULL
) ON [PRIMARY]

insert into SomeTable(Name, IP, Role, Grp) values('Server 1', '10.1', 'Primary', '1')
---
insert into SomeTable(Name, IP, Role, Grp) values('Server  2', '10.2', 'Secondary', '1')
---
insert into SomeTable(Name, IP, Role, Grp) values('Server 3', '10.3', 'Primary', '2')
---
insert into SomeTable(Name, IP, Role, Grp) values('Server 4', '10.4', 'Secondary', '2')
---

The problem:

Given the table below:

TABLEA

Id Name IP Role Group
1 Server1 10.1 Primary 1
2 Server2 10.2 Backup 1
3 Server3 10.3 Primary 2
4 Server4 10.4 Backup 2


We need a query to produce the below resultset:

Query result:
PrimaryName PrimaryIP BackupName BackupIP Group
Server1 10.1 Server2 10.2 1
Server3 10.3 Server4 10.4 2

Thank you in advance, and once more, only help if you have the time. This is not course work or any school project.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).

 

[gmmastros]

Select  Max(Case When Role = 'Primary' Then Name End) As PrimaryName,
        Max(Case When Role = 'Primary' Then IP End) As PrimaryIP,
        Max(Case When Role = 'Secondary' Then Name End) As SecondarName,
        Max(Case When Role = 'Secondary' Then IP End) As SecondaryIP,
        Grp
From    SomeTable
Group By Grp

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

How do you do it George? You're absolutely the best. Thank you. Much appreciated.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).

 

[Olaf Doschke]

There are not many choices, when you want a group of data. GROUP BY then is inevitable and also enforeces to use some aggregate function on any other field. This would also work with MIN, as there only is one member in each row per group.

I'd rather do it by split and join:

declare @computers as TABLE (
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[IP] [nvarchar](50) NULL,
	[Role] [nchar](10) NULL,
	[Grp] [nchar](10) NULL
);

insert into @computers(Name, IP, Role, Grp) values
   ('Server 1', '10.1', 'Primary', '1'),
   ('Server  2', '10.2', 'Secondary', '1'),
   ('Server 3', '10.3', 'Primary', '2'),
   ('Server 4', '10.4', 'Secondary', '2');


Select  PrimaryName, PrimaryIP, SecondaryName, SecondaryIP, pri.grp From
(Select Name as PrimaryName, IP as PrimaryIP, grp From @computers Where Role = 'Primary') pri
inner join 
(Select Name as SecondaryName, IP as SecondaryIP, grp From @computers Where Role = 'Secondary') sec
on pri.grp = sec.grp

And if you don't mind double field names and a double grp column in the result it can be shorter as in

Select * from
(Select Name,Ip,Grp From @computers Where Role = 'Primary') pri
inner join 
(Select Name,Ip,Grp From @computers Where Role = 'Secondary') sec
on pri.grp = sec.grp;

It might be favourable to use grouping for performance.
I always think you have to understand, what you use, or else you have the problem in expanding, extending or fixing it (maybe because of some change from outside) later.

Bye, Olaf.