SQL GroupBy & Get Non Group rows as columns-CodePudding

I've data as follows

AreaId	AreaName	Module
1	AAA	Square
2	AAA	Circle
3	BBB	Square
4	CCC	Square
5	CCC	Circle
6	DDD	Circle

I'm looking for some help in SQL to get the data as follows

AreaName	SquareArea	CircleArea
AAA	1	2
BBB	3	Null
CCC	4	5
DDD	Null	6

I've tried with Distinct. But I'm unable to prepare the SQL for the data result that I'm looking for

CodePudding user response：

DECLARE @table TABLE (AreaID INT IDENTITY, AreaName NVARCHAR(3), Module NVARCHAR(10))
INSERT INTO @table (AreaName, Module) VALUES
('AAA','Square'),('AAA','Circle'),('BBB','Square'),
('CCC','Square'),('CCC','Circle'),('DDD','Circle')

Example data is much easier to use when it's presented as a defined object.

SELECT AreaName, AVG(CASE WHEN Module = 'Square' THEN AreaID END) AS SquareArea, 
                 AVG(CASE WHEN Module = 'Circle' THEN AreaID END) AS CircleArea
  FROM @table t
 GROUP BY t.AreaName

You will need to decide which aggregation method you want to use though, MIN? MAX? AVG?

CodePudding user response：

This is a standard pivot.

SELECT AreaName, SquareArea = [Square], CircleArea = [Circle]
  FROM dbo.YourTableName
  PIVOT (MAX(AreaID) FOR Module IN ([Square],[Circle])) AS p;

Working example in this fiddle.