tsql inversione risultati query

maurizio verdirame 41 Reputation points
2021-08-14T14:24:06.097+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,657 questions
{count} votes

2 answers

Sort by: Most helpful
  1. maurizio verdirame 41 Reputation points
    2021-08-14T15:39:14.823+00:00

    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

    0 comments No comments

  2. Erland Sommarskog 112.7K Reputation points MVP
    2021-08-14T17:08:15.42+00:00

    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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.