How to query from a table to make a single row for multiple row values in SQL Server?

Question

I currently have a data set that has all information row-wise.

For example:

EmpID	Dept	ItemCode	IssueDate
10001	ELECT	400001	28/02/2020
10002	MECH	400001	20/03/2020
10001	ELECT	400001	01/03/2021
10001	ELECT	400001	05/04/2022
10002	MECH	400001	28/04/2021
10003	CIVIL	400001	28/02/2022

Output needs to be like this:

EmpID	Dept	ItemCode	2020	2021	2022
10001	ELECT	400001	28/02/2020	01/03/2021	05/04/2022
10002	MECH	400001	20/03/2020	28/04/2021
10003	CIVIL	400001			28/02/2022

I want to display the output for the last 3 years' data for employees. I would like to write a query in a generic way that displays the last 3 years' data.

Maybe this is what you're looking for: learn.microsoft.com/en-US/sql/t-sql/queries/… — JuanN
– JuanN, Commented Jun 25, 2022 at 18:16

nbk · Accepted Answer · 2022-06-25 18:59:40Z

it is called pivot, but as i don't have a 2012 sql server i don't know if pivoz was already there.

so you can use at least the classical way

CREATE TABLE items (
  "EmpID" INTEGER,
  "Dept" VARCHAR(5),
  "ItemCode" INTEGER,
  "IssueDate"  VARCHAR(10)
);

INSERT INTO items
  ("EmpID", "Dept", "ItemCode", "IssueDate")
VALUES
  ('10001', 'ELECT', '400001', '28/02/2020'),
  ('10002', 'MECH', '400001', '20/03/2020'),
  ('10001', 'ELECT', '400001', '01/03/2021'),
  ('10001', 'ELECT', '400001', '05/04/2022'),
  ('10002', 'MECH', '400001', '28/04/2021'),
  ('10003', 'CIVIL', '400001', '28/02/2022');
GO

SELECT
"EmpID", "Dept", "ItemCode"
, MAX(CASE WHEN YEAR(convert(DATE,"IssueDate", 103)) = 2020 THEN "IssueDate" ELSE NULL END) '2020'
, MAX(CASE WHEN YEAR(convert(DATE,"IssueDate", 103)) = 2021 THEN "IssueDate" ELSE NULL END) '2021'
, MAX(CASE WHEN YEAR(convert(DATE,"IssueDate", 103)) = 2022 THEN "IssueDate" ELSE NULL END) '20212'
FROM [items]
GROUP BY "EmpID", "Dept", "ItemCode"
GO

EmpID | Dept  | ItemCode | 2020       | 2021       | 20212     
----: | :---- | -------: | :--------- | :--------- | :---------
10001 | ELECT |   400001 | 28/02/2020 | 01/03/2021 | 05/04/2022
10002 | MECH  |   400001 | 20/03/2020 | 28/04/2021 | null      
10003 | CIVIL |   400001 | null       | null       | 28/02/2022

db<>fiddle here

Collectives™ on Stack Overflow

How to query from a table to make a single row for multiple row values in SQL Server?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related