question

maurizioverdirame-1554 avatar image
0 Votes"
maurizioverdirame-1554 asked EchoLiu-msft commented

query to change vertical to orizontal

the table described returns a tabular format of the data:

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


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

sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

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 @data and #data.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

maurizioverdirame-1554 avatar image
0 Votes"
maurizioverdirame-1554 answered Viorel-1 edited

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 @sql 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 (@sql)

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

If I understand correctly what this v is (I never use the PIVOT and UNPIVOT operators myself), that would be:

ORDER BY CASE v WHEN 'peso' THEN 1
            WHEN 'Altezza' THEN 2
            WHEN 'circpolso' THEN 3
         END
0 Votes 0 ·

For example, try adding 'order by' after p:

. . .) p order by charindex(f, ''peso, Altezza, circpolso'')')


0 Votes 0 ·
maurizioverdirame-1554 avatar image
0 Votes"
maurizioverdirame-1554 answered Viorel-1 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Joergx avatar image
0 Votes"
Joergx answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @maurizioverdirame-1554

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.



image.png (3.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.