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 blog
The 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),
('Black&Cream', '2014-02-02',NULL),
('White', '2015-03-03',1),
('Brown&White', '2015-08-08',1),
('Brown', '2016-01-01',1),
('Black', '2015-09-09',2),
('Cream', '2016-02-02',2)
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