query to change vertical to orizontal

maurizio verdirame 41 Reputation points
2021-08-14T16:19:02.56+00:00

the table described returns a tabular format of the data:

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 for my application is instead this

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

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

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

5 answers

Sort by: Most helpful
  1. Viorel 113.2K Reputation points
    2021-08-14T16:37:33.783+00:00

    Check if the next example can be adjusted for your needs:

    drop table if exists #data  
      
    select *   
    into #data  
    from @data  
      
    declare @cols varchar(max) = stuff((  
        select ',' + quotename([date])  
        from #data  
        order by [date]  
        for xml path('')  
        ), 1, 1, '')  
      
    declare @sql varchar(max) = concat(  
    'select ', @cols, '  
    from (  
        select [date], Field1, Field2, Field3, Field4  
        from #data  
    ) t  
    unpivot  
    (  
      v for f in (Field1, Field2, Field3, Field4)  
    ) u  
    pivot  
    (  
        max (v) for [date] in (', @cols, ')  
    ) p')  
      
    exec (@sql)  
    

    You can also try STRING_AGG instead of XML.

    If you have a real table, then use it directly instead of @alrt and #data.

    0 comments No comments

  2. maurizio verdirame 41 Reputation points
    2021-08-14T18:08:30.82+00:00

    many thanks, the example is fitting, I have to ask you if there is a way to maintain the order of the grouped fields (field1,field,2,field3 etc)

    using my table, the value of "peso", "Altezza", "CircPolso" in the results is not as in the written order.

    declare @cols varchar(max) = stuff((
    select ',' + quotename(DataVisita)
    from ValoriAntoprometrici
    order by [DataVisita]
    for xml path('')
    ), 1, 1,'')

    declare @alenzi varchar(max) = concat('select', @cols,
    ' from (
    select [DataVisita],peso, Altezza,circpolso
    from ValoriAntoprometrici
    ) t
    unpivot
    (
    v for f in (peso,altezza,circpolso)
    ) u
    pivot
    (
    max (v) for [DataVisita] in (', @cols, ')
    ) p')

    exec (@alenzi )


  3. maurizio verdirame 41 Reputation points
    2021-08-14T18:42:07.78+00:00

    big, really big, how can I successfully close this post?


  4. Joerg 62 116 Reputation points
    2021-08-15T18:26:51.707+00:00

    You can try this Query:

    declare @data table(ID int, date nvarchar(10), Field1 nvarchar(10), Field2 nvarchar(10), Field3 nvarchar(10),Field4 nvarchar(10))  
    insert into @data  
    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 @data  
      
      
    DECLARE @Xmldata XML = (SELECT * FROM @data FOR XML PATH('') )   
     drop table if exists #data  
      
    --Dynamic unpivoting  
    SELECT * INTO #data FROM (  
    SELECT * FROM (  
    SELECT ROW_NUMBER() OVER (PARTITION BY i.value('local-name(.)','varchar(100)') ORDER BY x.i) AS rn,  
    	   i.value('local-name(.)','varchar(100)') ColumnName,  
           i.value('.','varchar(100)') ColumnValue  
    FROM @xmldata.nodes('//*[text()]') x(i) ) tmp ) tmp1  
      
    -- SELECT * FROM @data		/* Unpivot-Daten anzeigen */  
      
    --Dynamic pivoting  
    DECLARE @Columns NVARCHAR(MAX),@query NVARCHAR(MAX)  
    SELECT @Columns = STUFF(  
     (SELECT  ', ' +QUOTENAME(CONVERT(VARCHAR,rn)) FROM  
     (SELECT DISTINCT rn FROM #data ) AS T FOR XML PATH('')),1,2,'')   
    SET @query = N'  
    SELECT ColumnName,' + @Columns + '  
    FROM  
    (  
      SELECT * FROM #data  
    )  i  
    PIVOT  
    (  
      MAX(ColumnValue) FOR rn IN ('  
      + @Columns  
      + ')  
    )  j ;';  
      
    EXEC (@query)  
    --PRINT @query  
    

    Regards.

    0 comments No comments

  5. EchoLiu-MSFT 14,571 Reputation points
    2021-08-16T03:03:13.917+00:00

    Hi @maurizio verdirame

    Welcome to the microsoft TSQL Q&A forum!

    Please also refer to:

    create table #data(ID int, date nvarchar(10), Field1 nvarchar(10),   
    Field2 nvarchar(10), Field3 nvarchar(10),Field4 nvarchar(10))  
      
    insert into #data  
    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 [10/07/21],[19/06/21],[29/05/21]   
    from (select date,Field1,Field2,Field3,Field4   
          from #data) t  
    unpivot ( dat for Field in(Field1,Field2,Field3,Field4)) up  
    pivot (max(dat) for date in([10/07/21],[19/06/21],[29/05/21])) p  
    

    Output:
    123453-image.png

    If you have any question, please feel free to let me know.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments