Multiple row data for perticluar item in single column

This is not particularly easy as you can only PIVOT against one column. Many solutions for pivoting against multiple columns suggest "merging" information e.g. get columns Jan-2016, Feb-2016, Mar-2016 and total based on those groupings, but that technique won't work here.

The technique below works, but I'm sure there must be a better way so I may be back! It's based on this blogThe dangerous beauty of the PIVOT operator in SQL Server |[^]

Firstly I created some sample data as follows:

CREATE TABLE [Goat](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Color] [varchar](30) NULL,
	[DOB] [date] NULL,
	[Mother] [int] NULL
) ON [PRIMARY]

GO

INSERT INTO Goat VALUES
('Brown&White',	'2013-01-01',NULL),  -- First Mother M1
('Black&Cream',	'2014-02-02',NULL),  -- Second Mother M2
('White',	'2015-03-03',1),		-- 1st Child of M1
('Brown&White',	'2015-08-08',1),	-- 2nd Child of M1
('Brown',	'2016-01-01',1),		-- 3rd Child of M1
('Black',	'2015-09-09',2),		-- 1st Child of M2
('Cream',	'2016-02-02',2)			-- 2nd Child of M2

This query then gets the data you want:

;with cte as
(
	select Mother, id, 
	CAST(ROW_NUMBER() OVER (PARTITION BY Mother ORDER BY DOB) AS VARCHAR) as rn,
	Color, CAST(DATEDIFF(mm, DOB, GETDATE()) AS VARCHAR) as Age
	FROM Goat WHERE Mother IS NOT NULL
), cte2 as
(
SELECT Mother, id, 'Child'+rn as Element, 'Child' + rn as Value
from cte
UNION 
SELECT Mother, id, 'Color'+rn as Element, Color as Value
from cte
UNION
SELECT Mother, id, 'Age'+rn as Element, Age as Value
from cte
)
SELECT
	Mother,
	MAX(Child1) As Child1, Max(Color1) As Color1, Max(Age1) As Age1,
	MAX(Child2) As Child2, Max(Color2) As Color2, Max(Age2) As Age2,
	MAX(Child3) As Child3, Max(Color3) As Color3, Max(Age3) As Age3
FROM cte2
PIVOT(MAX(Value) FOR Element IN (Child1,Color1, Age1, Child2, Color2, Age2, Child3, Color3, Age3)) AS t
GROUP BY Mother

NOTES:
1. The first bit (cte) returns this result for my data

Mother  Id      Child   Color           Age(in months)
1	3	1	White	        13
1	4	2	Brown&White	8
1	5	3	Brown	        3
2	6	1	Black	        7
2	7	2	Cream	        2

This is where you would put the query that produces your current results

2. The next bit (cte2) is creating a set of key-value pairs like this

Mother  Id      Element Value
1	3	Age1	13
1	3	Child1	Child1
1	3	Color1	White
1	4	Age2	8
1	4	Child2	Child2
1	4	Color2	Brown&White
1	5	Age3	3
1	5	Child3	Child3
1	5	Color3	Brown
2	6	Age1	7
2	6	Child1	Child1
2	6	Color1	Black
2	7	Age2	2
2	7	Child2	Child2
2	7	Color2	Cream

3. The last bit is generating the data we want using GROUP and PIVOT and delivers these results

1  Child1  White   13	Child2	Brown&White	8  Child3  Brown 3
2  Child1  Black   7	Child2	Cream	        2  NULL	   NULL  NULL

4. The obvious disadvantage to this method is that it assumes each goat only has a maximum of 3 kids. If this fits your model then you don't have to do anything else, but it's unrealistic. To overcome that restriction you are going to have to create dynamic sql using something like

select max(c) from (SELECT count(id) c from Goat Group by Mother) as counter

to work out how many columns you need up front. If I get time I'll come back with a example of that

[EDIT] Here is a dynamic SQL version that will work for any number of kids

DECLARE @maxChildren int = (select max(c) from (SELECT count(id) c from Goat Group by Mother) as counter)

DECLARE @dynamicMAXbit varchar(max) = ''
DECLARE @dynamicCOLbit varchar(max) = ''
DECLARE @loop int = 1
DECLARE @loopC varchar(3)
WHILE @loop <= @maxChildren
BEGIN
	SET @loopC = CAST(@loop AS varchar) 
	SET @dynamicMAXbit = @dynamicMAXbit + 'MAX(Child' + @loopC + ') As Child' + @loopC 
	SET @dynamicMAXbit = @dynamicMAXbit + ', Max(Color' + @loopC + ') As Color' + @loopC
	SET @dynamicMAXbit = @dynamicMAXbit + ', Max(Age' + @loopC + ') As Age' + @loopC

	SET @dynamicCOLbit = @dynamicCOLbit + 'Child' + @loopC + ',Color' + @loopC + ', Age' + @loopC

	IF @loop < @maxChildren
	BEGIN
		SET @dynamicMAXbit = @dynamicMAXbit + ','
		SET @dynamicCOLbit = @dynamicCOLbit + ','
	END
	SET @loop = @loop + 1
END
DECLARE @sql varchar(max)
SET @sql = ';with cte as (select Mother, id, '
SET @sql = @sql + 'CAST(ROW_NUMBER() OVER (PARTITION BY Mother ORDER BY DOB) AS VARCHAR) as rn,'
SET @sql = @sql + 'Color, CAST(DATEDIFF(mm, DOB, GETDATE()) AS VARCHAR) as Age FROM Goat WHERE Mother IS NOT NULL'
SET @sql = @sql + '), cte2 as (SELECT Mother, id, ''Child''+rn as Element, ''Child'' + rn as Value '
SET @sql = @sql + 'from cte UNION SELECT Mother, id, ''Color''+rn as Element, Color as Value '
SET @sql = @sql + 'from cte UNION SELECT Mother, id, ''Age''+rn as Element, Age as Value '
SET @sql = @sql + 'from cte ) SELECT Mother,' + @dynamicMAXbit + ' FROM cte2 '
SET @sql = @sql + 'PIVOT(MAX(Value) FOR Element IN (' + @dynamicCOLbit + ')) AS t GROUP BY Mother'

EXEC sp_sqlexec @sql