-3

enter image description here

How do I turn the top dataset into the bottom dataset, so that I can add it to another joined table?

I was trying Cross joins and Pivots but nothing seemed to work.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
phillismable
  • 23
  • 5
  • 1
    [Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) You also say you've "tried" some things, but you forgot to include those attempts. Show us them and explain *why* they aren't working. – Thom A Dec 30 '22 at 13:04
  • Does this answer your question? [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) – Tim Jarosz Dec 30 '22 at 13:12

1 Answers1

1

Please search before asking. Simple pivot examples are already on this site numerous times. Here's another simple example using PIVOT:

CREATE TABLE animals (
  ID int
  , ChildID int
  , [Name] nvarchar(50)
  , Age int
);

INSERT INTO animals (ID, ChildID, [Name], Age)
VALUES (1, 654, 'Cat', 1)
  , (2, 654, 'Dog', 2)
  , (5, 655, 'Cat', 4)
  , (6, 655, 'Dog', 3)
;

SELECT ChildID
  , PivotTable.[Cat]
  , PivotTable.[Dog]
FROM (
  SELECT ChildID, [Name], Age
  From animals
) as sourceTable
PIVOT (
  SUM([Age])
  FOR [Name] IN ([Cat],[Dog])
) as pivotTable
ORDER BY ChildID
;
ChildID Cat Dog
654 1 2
655 4 3

fiddle

Tim Jarosz
  • 1,133
  • 1
  • 8
  • 15
  • Hi Thanks you so much for helping me out, I did try the pivot but had this part incorrect FOR [Name] IN ([Cat],[Dog]) your version worked like a charm x – phillismable Dec 30 '22 at 13:16