SOLVED

How to write SQL getting data from multiple rows

Copper Contributor
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[People]') AND type in (N'U'))
DROP TABLE [People]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [People](
	[PersonID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](16) NOT NULL,
	[Married] [date] NULL,
 CONSTRAINT [PK_People] PRIMARY KEY CLUSTERED 
(
	[PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO
	People ([Name], Married)
VALUES
	('John Doe', '2000-02-20'),
	('Sam Smith', '2000-03-03'),
	('Jack Beanstalk', NULL),
	('Mary Doe', '2000-02-20'),
	('Harry Potter', NULL),
	('Patty Smith', '2000-03-03');

Desired Output

+---------------------+------------+
| Names                     | Date          |
+---------------------+------------+
| John and Mary Doe | 2000-02-20 |
| Sam and Patty Smith | 2000-03-03 |
+---------------------+------------+

6 Replies
That's really to less on information.
Please post table design as DDL, some sample data as DML statement and the expected result.

@olafhelperI have rewritten the OP.

best response confirmed by SezMe (Copper Contributor)
Solution
I Think You could use this Query

SELECT STRING_AGG(Name,' And ') Names, Married Date FROM People GROUP BY Married

Regards
Arshad

@SezMe , so now you want to "guess" just by the marriage date the couples? Do you know, how many people marry per day? And which one first and second in the result?

DECLARE @People AS TABLE (
	PersonID int IDENTITY(1,1) NOT NULL,
	Name varchar(16) NOT NULL,
	Married date NULL);

INSERT INTO
	@People (Name, Married)
VALUES
	('John Doe', '2000-02-20'),
	('Sam Smith', '2000-03-03'),
	('Jack Beanstalk', NULL),
	('Mary Doe', '2000-02-20'),
	('Harry Potter', NULL),
	('Patty Smith', '2000-03-03');

SELECT *
FROM @People AS P1
     INNER JOIN
     @People AS P2
         ON P1.Married = P2.Married
WHERE P1.Name <> P2.Name

@Arshad440Thanks.  Just needed to add "having Married is not null"

@olafhelper  No guessing required.  A more precise question is: Do I know how many people marry per day who will ever be in my database?  You will be happy to know I do know how to answer that question: 2.

 

If the order had mattered, I would have said so.

1 best response

Accepted Solutions
best response confirmed by SezMe (Copper Contributor)
Solution
I Think You could use this Query

SELECT STRING_AGG(Name,' And ') Names, Married Date FROM People GROUP BY Married

Regards
Arshad

View solution in original post