Forum Discussion

SezMe's avatar
SezMe
Copper Contributor
Jun 23, 2024
Solved

How to write SQL getting data from multiple rows

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 |
+---------------------+------------+

  • Arshad440's avatar
    Arshad440
    Jun 28, 2024
    I Think You could use this Query

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

    Regards
    Arshad

6 Replies

  • Arshad440's avatar
    Arshad440
    Brass Contributor
    Jun 28, 2024
    I Think You could use this Query

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

    Regards
    Arshad
  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Jun 24, 2024
    That's really to less on information.
    Please post table design as DDL, some sample data as DML statement and the expected result.
      • olafhelper's avatar
        olafhelper
        Bronze Contributor
        Jun 28, 2024

        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

Resources