List 4 row into 4 column with same ID
-
Hi,
As I'm developing for client that require show all information in same row to ID. For example the first image
It is list of Animal with ID 101 to 104 which it count number of animal per year. which I have SQL already which is not a problem.
Now I need this just same ID and animal. but the new column with recent year with number.
How do I solve this query?
-
This is easily achieved with a cross-tab query
CREATE TABLE #Animal (ID int, Animal varchar(20), Number int, [Year] int);
INSERT INTO #Animal ( ID, Animal, Number, [Year] )
VALUES ( 101, 'Rabbit', 1, 2020 )
, ( 101, 'Rabbit', 5, 2017 )
, ( 101, 'Rabbit', 3, 2016 )
, ( 101, 'Rabbit', 5, 2015 )
----------------------------
, ( 102, 'Bear', 7, 2019 )
, ( 102, 'Bear', 2, 2018 )
, ( 102, 'Bear', 1, 2016 )
, ( 102, 'Bear', 5, 2015 )
----------------------------
, ( 103, 'Lion', 5, 2019 )
, ( 103, 'Lion', 8, 2017 )
, ( 103, 'Lion', 6, 2016 )
, ( 103, 'Lion', 2, 2015 )
----------------------------
, ( 104, 'Kangaroo', 3, 2020 )
, ( 104, 'Kangaroo', 2, 2018 )
, ( 104, 'Kangaroo', 5, 2017 )
, ( 104, 'Kangaroo', 7, 2016 );
WITH cteData AS (
SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY a.ID ORDER BY a.Year DESC)
FROM #Animal AS a
)
SELECT d.ID, d.Animal
, Recent1 = MAX(CASE WHEN rn = 1 THEN CAST(d.Number as varchar(10)) + ' (' + CAST(d.[Year] as varchar(10)) + ')' ELSE NULL END)
, Recent2 = MAX(CASE WHEN rn = 2 THEN CAST(d.Number as varchar(10)) + ' (' + CAST(d.[Year] as varchar(10)) + ')' ELSE NULL END)
, Recent3 = MAX(CASE WHEN rn = 3 THEN CAST(d.Number as varchar(10)) + ' (' + CAST(d.[Year] as varchar(10)) + ')' ELSE NULL END)
, Recent4 = MAX(CASE WHEN rn = 4 THEN CAST(d.Number as varchar(10)) + ' (' + CAST(d.[Year] as varchar(10)) + ')' ELSE NULL END)
FROM cteData AS d
WHERE rn <= 4
GROUP BY d.ID, d.Animal
ORDER BY d.ID; -
Thank you very much! it is useful 🙂
-
Please see the following article if you want to know more about CROSSTAB queries and how they work.
https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-converting-rows-to-columns-1
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply