SQL pivot multiples columns

Question

TT	ID	No	Date_input	BR
1	06041098	780000	01/01/2015	111
2	01506000299	780000	11/07/2022	111
1	06048903	783111	01/01/2015	123
2	015173000562	783111	04/25/2023	123
1	01517300	792523	02/02/2022	150
2	011105981	792523	03/21/2022	150
3	0346166413164	792523	04/25/2023	150

I am having "Mytable" above and would like to get pivot, and my syntax is:

select no, br, \[1\] as ID1, \[2\] as ID2, \[3\] as ID3
from (select TT, id, no, Br from Mytable) Table_Pivot
pivot
(min(ID) for TT in (\[1\] , \[2\] , \[3\])) bang

However, I would like add Date_input in my pivot as result show below

NoB	Br	ID1	Date_input1	ID2	Date_input2	ID3	Date_input3
780000	111	06041098	01/01/2015	01506000299	11/07/2022
783111	123	06048903	01/01/2015	015173000562	04/25/2023
792523	150	01517300	02/02/2022	011105981	03/21/2022	0346166413164	04/25/2023

Does this answer your question? [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — bonCodigo, May 05 '23 at 01:54
[How to pivot multiple columns](https://stackoverflow.com/questions/38067490/in-sql-server-how-to-pivot-for-multiple-columns) — Dale K, May 05 '23 at 02:36

score 1 · Answer 1 · edited May 05 '23 at 05:47

The simplest way (in SQL Server) to achieve "pivots" of multiple columns is to do it the old way with "conditional aggregates" (case expressions inside aggregate functions) e.g.

-- Traditional Pivot query using conditional aggregates
SELECT No AS NoB,
       BR,
       MAX(CASE WHEN TT = 1 THEN ID END) AS ID1,
       MAX(CASE WHEN TT = 1 THEN Date_input END) AS Date_input1,
       MAX(CASE WHEN TT = 2 THEN ID END) AS ID2,
       MAX(CASE WHEN TT = 2 THEN Date_input END) AS Date_input2,
       MAX(CASE WHEN TT = 3 THEN ID END) AS ID3,
       MAX(CASE WHEN TT = 3 THEN Date_input END) AS Date_input3
FROM   mytable
GROUP BY No, BR;

NoB	BR	ID1	Date_input1	ID2	Date_input2	ID3	Date_input3
780000	111	06041098	2015-01-01	01506000299	2022-11-07	null	null
783111	123	06048903	2015-01-01	015173000562	2023-04-25	null	null
792523	150	01517300	2022-02-02	011105981	2022-03-21	0346166413164	2023-04-25

fiddle

For formatting dates to mm/dd/yyyy (style number 101)

   MAX(CASE WHEN TT = 1 THEN ID END) AS ID1,
   MAX(CASE WHEN TT = 1 THEN CONVERT(varchar, Date_input, 101) END) AS Date_input1,
   MAX(CASE WHEN TT = 2 THEN ID END) AS ID2,
   MAX(CASE WHEN TT = 2 THEN CONVERT(varchar, Date_input, 101) END) AS Date_input2,
   MAX(CASE WHEN TT = 3 THEN ID END) AS ID3,
   MAX(CASE WHEN TT = 3 THEN CONVERT(varchar, Date_input, 101) END) AS Date_input3

Tks Maxwel, I have done this with ur traditional suggestion. — Lucio, May 05 '23 at 09:34

SQL pivot multiples columns

1 Answers1