tsql inversione risultati query

Question

ho una tabella di tre righe che si presenta tipicamente:

date dato1 dato2 dato3 dato4 dato5
date dato1 dato2 dato3 dato4 dato5
date dato1 dato2 dato3 dato4 dato5

e che devo rappresentare in questo modo :
date date date
dato1 dato1 dato1
dato2 dato2 dato2
dato3 dato3 dato3
dato4 dato4 dato4
dato5 dato5 dato5

Answer 1

You first need to unpivot the fields and then pivot on the date:

; WITH unpiv AS (
   SELECT a.ID, a.date, V.name, V.value
   FROM   @data a
   CROSS  APPLY (VALUES ('Field1', a.Field1),
                        ('Field2', a.Field2),
                        ('Field3', a.Field3),
                        ('Field4', a.Field4)) AS V(name, value)
)
SELECT ID, name, 
       MIN(CASE date WHEN '10/07/21' THEN value END) AS [10/07/21],
       MIN(CASE date WHEN '19/06/21' THEN value END) AS [19/06/21],
       MIN(CASE date WHEN '29/05/21' THEN value END) AS [29/05/21]
FROM   unpiv
GROUP BY ID, name

Now, I can guess that you will say that the dates can be dynamic, both in values and in how many dates there are. But a query in SQL always returns a fixed number of columns with fixed column names. You can build a query dynamically, but before there, you should ask yourself, if this operation can be performed better elsewhere. A dynamic pivot is a presentational device, and may be better performed in the presentation layer.

By the way, while this is just a demo table, I still like to point out that storing dates in string columns is a exquisitely bad idea, and is very likely to cause you trouble.

Answer 2

sorry it's the first time and I started the question before translating it.

typically a table like this below returns in tabular format

declare @alrt table(ID int, date nvarchar(10), Field1 nvarchar(10), Field2 nvarchar(10), Field3 nvarchar(10),Field4 nvarchar(10))

insert into @alrt
select 1, '10/07/21', '10' ,'30','60','90'union all
select 1, '19/06/21 ', '15','35','65','95' union all
select 1, '29/05/21 ', '20','60','80','100'
select * from @alrt

result
1 10/07/21 10 30 60 90
1 19/06/21 15 35 65 95
1 29/05/21 20 60 80 100

the result I have to get is instead this

10/07/21 19/06/21 29/05/21

10 15 20
30 35 60
60 65 80
90 95 100