query to change vertical to orizontal

Question

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

Answer 1

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.

Answer 2

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 )

Answer 3

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

Answer 4

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.

Answer 5

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:

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.