PivotTable with column dates

Question

PivotTable with column dates

maurizio verdirame 41

good morning everyone, I would need help to pull out the data from a join of tables that still give the result of the example

drop table #test
CREATE TABLE #Test(
[id] [int] NULL,
[data] [date] NULL,
[field1] [float] NULL,
[field2] [float] NULL,
[field3] [float] NULL,
[field4] [float] NULL
) ON [PRIMARY]
GO

INSERT INTO #Test
(id, data, field1, field2, field3, field4)
VALUES (1, CONVERT(DATETIME, '1955-01-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(1, CONVERT(DATETIME, '2022-01-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(2, CONVERT(DATETIME, '2022-02-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(3, CONVERT(DATETIME, '2022-03-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(4, CONVERT(DATETIME, '2022-04-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(5, CONVERT(DATETIME, '2022-05-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(6, CONVERT(DATETIME, '2022-06-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(7, CONVERT(DATETIME, '2022-07-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(8, CONVERT(DATETIME, '2022-08-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)

typical result:
select * from #test

     date              field1 field2 field3 field4

1 2022-01-16 2,3 3,2 4,5 6,8
2 2022-02-16 2,3 3,2 4,5 6,8
3 2022-03-16 2,3 3,2 4,5 6,8
4 2022-04-16 2,3 3,2 4,5 6,8
5 2022-05-16 2,3 3,2 4,5 6,8
6 2022-06-16 2,3 3,2 4,5 6,8
7 2022-07-16 2,3 3,2 4,5 6,8
8 2022-08-16 2,3 3,2 4,5 6,8

I would like to have (I desperately need it!) a table with the names of the fields in the first column, the other columns with the dates as a head as in the following result.

field 2022-01-16 2022-02-16 2022-03-16 2022-04-16 2022-05-16 2022-06-16 2022-07-16 2022-08-16
field1 2,3 2,3 2,3 2,3 2,3 2,3 2,3 2,3
field2 3,2 3,2 3,2 3,2 3,2 3,2 3,2 3,2
field3 4,5 4,5 4,5 4,5 4,5 4,5 4,5 4,5
field4 6,8 6,8 6,8 6,8 6,8 6,8 6,8 6,8

I thank anyone who will help me

0 comments

Answer accepted by question author

Jingyang Li 5,901 Volunteer Moderator

drop table if exists  Test
CREATE TABLE  Test(
[id] [int] NULL,
[data] [date] NULL,
[field1] [float] NULL,
[field2] [float] NULL,
[field3] [float] NULL,
[field4] [float] NULL
)  
GO


INSERT INTO Test
(id, data, field1, field2, field3, field4)
VALUES (1, CONVERT(DATETIME, '1955-01-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(1, CONVERT(DATETIME, '2022-01-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(2, CONVERT(DATETIME, '2022-02-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(3, CONVERT(DATETIME, '2022-03-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(4, CONVERT(DATETIME, '2022-04-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(5, CONVERT(DATETIME, '2022-05-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(6, CONVERT(DATETIME, '2022-06-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(7, CONVERT(DATETIME, '2022-07-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(8, CONVERT(DATETIME, '2022-08-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)



Declare @sql1 as NVarchar(4000)
Declare @sql2 as NVarchar(4000)
Declare @Cols1 as NVarchar(4000)
Declare @Cols2 as NVarchar(4000)

Set @Cols1=null

Select @Cols1 =  COALESCE(@Cols1 + ', ', '') + '('+QUOTENAME(COLUMN_NAME,'''')+',' + QUOTENAME(COLUMN_NAME)+')'
FROM  [INFORMATION_SCHEMA].[COLUMNS]
WHERE TABLE_NAME='Test' and COLUMN_NAME<>'data' and COLUMN_NAME<>'id'



  Select @sql1=';with mycte as (Select
data, col,val
FROM Test  
CROSS APPLY (Values ' + @Cols1 + ' )  d(col,val)
where id<>1
)
SELECT   @Cols2  = STUFF( (SELECT  '','' + ''Max(CASE WHEN data='' + quotename(data,'''''''') + '' THEN val else null end ) as '' + quotename(data)  + char(10)+char(13)
                             FROM mycte
                             Group by data
                             order by  data                           
  FOR XML PATH(''''), TYPE).value(''.'', ''varchar(max)''), 1, 1, ''''); '



 EXEC sp_executesql @sql1,N'@Cols2 NVARCHAR(4000) output',@Cols2  output



 Set @sql2  =N';with mycte as (Select data, col,val
FROM  Test t
CROSS APPLY (Values ' + @Cols1 + ' )  d(col,val) )
Select  col, '+   @Cols2 + '  from  mycte   Group by col';

EXEC sp_executesql @sql2


drop table  Test

Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-05-13T15:24:12.46+00:00

One more good option:
Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-05-13T15:32:15.223+00:00

Select @Cols1 = COALESCE(@Cols1 + ', ', '') + '('+QUOTENAME(COLUMN_NAME,'''')+',' + QUOTENAME(COLUMN_NAME)+')'
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE TABLE_NAME='Test' and COLUMN_NAME<>'data' and COLUMN_NAME<>'id'

use the following for temp table (replace the table name with temp table name inside code):

Select @Cols1 = COALESCE(@Cols1 + ', ', '') + '('+QUOTENAME(COLUMN_NAME,'''')+',' + QUOTENAME(COLUMN_NAME)+')'

FROM tempdb.INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_NAME LIKE '#Test%'
and COLUMN_NAME<>'data' and COLUMN_NAME<>'id'
maurizio verdirame 41 Reputation points

2022-05-13T16:25:40.307+00:00

dear JingyangLi I tried on the actual data your solution, thank you because it solves my question, the only thing I do not understand is the behavior of the date. in the example I posted the date field is date, in the production reality it is datetime. In the results it comes out like this: May 2022 05 12:00AM. instead of 2022-05-05. can you suggest me a solution? thanks for the answer
maurizio verdirame 41 Reputation points

2022-05-13T16:34:11.703+00:00

I solved by copying in test the date field with the 'Convert' now but I ask you how I can have a result suitable for my language (Italian) for example the
date format is dd / MM / yyyy thanks for the attention
maurizio verdirame 41 Reputation points

2022-05-13T16:40:10.317+00:00

solved this too, thank you
for your attention
Naomi Nosonovsky 8,906 Reputation points

2022-05-13T16:48:32.227+00:00

Just curious if you tried my solution too (which is not as generic, but it was based on the sample provided).
Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-05-13T16:49:13.937+00:00

A datetime in database does not have a format at all. But if you want to format your datetime in your query, you can use format it to your culture:
in your case, like this:
Select Format(CONVERT(datetime, '2022-01-16 00:00:00', 102),'dd/MM/yyyy')
maurizio verdirame 41 Reputation points

2022-05-13T17:33:36.517+00:00

yes I have, just, seen and I tried, thank you because I will use it in another part of the project.
thanks also to you and excuse me if I did not answer you immediately.
maurizio verdirame 41 Reputation points

2022-05-13T18:26:06.53+00:00

sorry if I bother you again, I realized that in my solution I can not sort the dates, which come out random
I used your code this way:
I entered in the test table the production data I needed

the results are these:
03-12-2016 05-11-2016 07-05-2016 10-09-2016 14-01-2017 16-07-2016
Altezza 1,68 1,68 1,68 1,68 1,68 1,68 1,68 1,68
Circ.Addominale 80 82 87,5 82,5 80 85 79 86
Circ.Avambraccio 24,5 24,5 24,5 24,5 24,5 24,5 23,5 24,5
Circ.Braccio 25 26,5 27,5 26 25 26 25 26
Circ.Braccio Pieg 26 25,5 28 26 26 26,5 26 26,5
Circ.Coscia Mediana 55,5 56 62 56,5 56 58 56 58
Circ.Fianchi 92 92,5 99 93 92 95 92 95
Circ.Polpaccio 35 35,5 35,5 35,5 35 35,5 34,5 35,5
Circ.Polso 15 15 15 15 15 15 15 15
Circ.Radice Coscia 0 0 0 0 0 0 0 0
Circ.Sopra Patellare 0 0 0 0 0 0 0 0
Circ.Torace 89 90 94 90 89 89 90 89
Circ.Vita 69 71,5 75 72,5 69 72 70 74
Diam.Caviglia 0 0 0 0 0 0 0 0
Diam.Ginocchio 0 0 0 0 0 0 0 0
Diam.Gomito 0 0 0 0 0 0 0 0
Diam.Polso 0 0 0 0 0 0 0 0
Peso 59,2 59,4 66 60,3 59,5 61,7 58,5 62,7
do you think I can sort the dates?
thanks in advance
Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-05-13T19:03:51.803+00:00

The comment is so deep now. I repost the solution with an Order by column to address your date sorting order,

3 additional answers

Your answer

Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-05-13T15:24:12.46+00:00

One more good option:
Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-05-13T15:32:15.223+00:00

Select @Cols1 = COALESCE(@Cols1 + ', ', '') + '('+QUOTENAME(COLUMN_NAME,'''')+',' + QUOTENAME(COLUMN_NAME)+')'
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE TABLE_NAME='Test' and COLUMN_NAME<>'data' and COLUMN_NAME<>'id'

use the following for temp table (replace the table name with temp table name inside code):

Select @Cols1 = COALESCE(@Cols1 + ', ', '') + '('+QUOTENAME(COLUMN_NAME,'''')+',' + QUOTENAME(COLUMN_NAME)+')'

FROM tempdb.INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_NAME LIKE '#Test%'
and COLUMN_NAME<>'data' and COLUMN_NAME<>'id'
maurizio verdirame 41 Reputation points

2022-05-13T16:25:40.307+00:00

dear JingyangLi I tried on the actual data your solution, thank you because it solves my question, the only thing I do not understand is the behavior of the date. in the example I posted the date field is date, in the production reality it is datetime. In the results it comes out like this: May 2022 05 12:00AM. instead of 2022-05-05. can you suggest me a solution? thanks for the answer
maurizio verdirame 41 Reputation points

2022-05-13T16:34:11.703+00:00

I solved by copying in test the date field with the 'Convert' now but I ask you how I can have a result suitable for my language (Italian) for example the
date format is dd / MM / yyyy thanks for the attention
maurizio verdirame 41 Reputation points

2022-05-13T16:40:10.317+00:00

solved this too, thank you
for your attention
Naomi Nosonovsky 8,906 Reputation points

2022-05-13T16:48:32.227+00:00

Just curious if you tried my solution too (which is not as generic, but it was based on the sample provided).
Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-05-13T16:49:13.937+00:00

A datetime in database does not have a format at all. But if you want to format your datetime in your query, you can use format it to your culture:
in your case, like this:
Select Format(CONVERT(datetime, '2022-01-16 00:00:00', 102),'dd/MM/yyyy')
maurizio verdirame 41 Reputation points

2022-05-13T17:33:36.517+00:00

yes I have, just, seen and I tried, thank you because I will use it in another part of the project.
thanks also to you and excuse me if I did not answer you immediately.
maurizio verdirame 41 Reputation points

2022-05-13T18:26:06.53+00:00

sorry if I bother you again, I realized that in my solution I can not sort the dates, which come out random
I used your code this way:
I entered in the test table the production data I needed

the results are these:
03-12-2016 05-11-2016 07-05-2016 10-09-2016 14-01-2017 16-07-2016
Altezza 1,68 1,68 1,68 1,68 1,68 1,68 1,68 1,68
Circ.Addominale 80 82 87,5 82,5 80 85 79 86
Circ.Avambraccio 24,5 24,5 24,5 24,5 24,5 24,5 23,5 24,5
Circ.Braccio 25 26,5 27,5 26 25 26 25 26
Circ.Braccio Pieg 26 25,5 28 26 26 26,5 26 26,5
Circ.Coscia Mediana 55,5 56 62 56,5 56 58 56 58
Circ.Fianchi 92 92,5 99 93 92 95 92 95
Circ.Polpaccio 35 35,5 35,5 35,5 35 35,5 34,5 35,5
Circ.Polso 15 15 15 15 15 15 15 15
Circ.Radice Coscia 0 0 0 0 0 0 0 0
Circ.Sopra Patellare 0 0 0 0 0 0 0 0
Circ.Torace 89 90 94 90 89 89 90 89
Circ.Vita 69 71,5 75 72,5 69 72 70 74
Diam.Caviglia 0 0 0 0 0 0 0 0
Diam.Ginocchio 0 0 0 0 0 0 0 0
Diam.Gomito 0 0 0 0 0 0 0 0
Diam.Polso 0 0 0 0 0 0 0 0
Peso 59,2 59,4 66 60,3 59,5 61,7 58,5 62,7
do you think I can sort the dates?
thanks in advance
Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-05-13T19:03:51.803+00:00

The comment is so deep now. I repost the solution with an Order by column to address your date sorting order,

Answer 1

Try this solution to sort dates correctly:

drop table IF EXISTS #test
CREATE TABLE #Test(
[id] [int] NULL,
[data] [date] NULL,
[field1] [float] NULL,
[field2] [float] NULL,
[field3] [float] NULL,
[field4] [float] NULL
) ON [PRIMARY]
GO


INSERT INTO #Test
(id, data, field1, field2, field3, field4)
VALUES (1, CONVERT(DATETIME, '1955-01-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(1, CONVERT(DATETIME, '2022-01-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(2, CONVERT(DATETIME, '2022-02-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(3, CONVERT(DATETIME, '2022-03-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(4, CONVERT(DATETIME, '2022-04-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(5, CONVERT(DATETIME, '2022-05-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(6, CONVERT(DATETIME, '2022-06-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(7, CONVERT(DATETIME, '2022-07-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(8, CONVERT(DATETIME, '2022-08-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)

DECLARE @SQL NVARCHAR(MAX), @cols NVARCHAR(MAX)

SELECT @cols = STRING_AGG(QUOTENAME(FORMAT(data, 'dd-MM-yyyy')), ',') WITHIN GROUP (ORDER BY data) FROM #Test;
--SELECT @cols = STRING_AGG(QUOTENAME(FORMAT(data, 'yyyy-MM-dd')), ',') WITHIN GROUP (ORDER BY data) FROM #Test;
SET @sql = ';WITH cte AS (SELECT Id, [data], FieldName, fieldValue
FROM #Test t CROSS APPLY (VALUES (''field1'', field1), (''field2'', field2), (''field3'', field3), (''field4'', field4)) F (fieldName, fieldValue))

SELECT * FROM (select FORMAT(data, ''dd-MM-yyyy'') as [Data], FieldName, FieldValue from cte) X PIVOT (max(fieldValue) for [data] in (' + @cols + ')) pvt'

execute (@SQL);

Answer 2

drop table if exists  #Test  
CREATE TABLE  #Test(  
[id] [int] NULL,  
[data] varchar(10) NULL,  
[field1] [float] NULL,  
[field2] [float] NULL,  
[field3] [float] NULL,  
[field4] [float] NULL  
)    
GO  
  
  
INSERT INTO #Test  
(id, data, field1, field2, field3, field4)  
VALUES  
 (1, '05-11-2016', 2.3, 3.2, 4.5, 6.8)  
,(2, '07-05-2016', 2.3, 3.2, 4.5, 6.8)  
,(3, '10-09-2016', 2.3, 3.2, 4.5, 6.8)  
,(4, '14-01-2017', 2.3, 3.2, 4.5, 6.8)  
,(5,'16-07-2016', 2.3, 3.2, 4.5, 6.8)  
,(6, '03-12-2016', 2.3, 3.2, 4.5, 6.8)  
   
 --SELECT * FROM tempdb.INFORMATION_SCHEMA.COLUMNS  WHERE  TABLE_NAME LIKE '#Test%'  
  
  
Declare @sql1 as NVarchar(4000)  
Declare @sql2 as NVarchar(4000)  
Declare @Cols1 as NVarchar(4000)  
Declare @Cols2 as NVarchar(4000)  
    
Set @Cols1=null  
    
Select @Cols1 =  COALESCE(@Cols1 + ', ', '') + '('+QUOTENAME(COLUMN_NAME,'''')+',' + QUOTENAME(COLUMN_NAME)+')'  
  
FROM tempdb.INFORMATION_SCHEMA.COLUMNS c   
WHERE  TABLE_NAME LIKE '#Test%'  
 and COLUMN_NAME<>'data' and COLUMN_NAME<>'id'  
   
  Select @sql1=';with mycte as (Select  
data, col,val  
FROM #Test    
CROSS APPLY (Values ' + @Cols1 + ' )  d(col,val)  
where id<>1  
)  
SELECT   @Cols2  = STUFF( (SELECT  '','' + ''Max(CASE WHEN data='' + quotename(data,'''''''') + '' THEN val else null end ) as '' + quotename(data)  + char(10)+char(13)  
                             FROM mycte  
                             Group by data  
  
                           Order by  try_cast(Convert( date,data,103) as date)   
							  
  FOR XML PATH(''''), TYPE).value(''.'', ''varchar(max)''), 1, 1, ''''); '  
    
    
   
 EXEC sp_executesql @sql1,N'@Cols2 NVARCHAR(4000) output',@Cols2  output  
   
   
     
 Set @sql2  =N';with mycte as (Select data, col,val  
FROM  #Test t  
CROSS APPLY (Values ' + @Cols1 + ' )  d(col,val) )  
Select  col, '+   @Cols2 + '  from  mycte   Group by col';  
   
EXEC sp_executesql @sql2  
   
    
drop table  #Test

maurizio verdirame 41 Reputation points

2022-05-14T10:33:35.137+00:00

thanks the information was very useful,
solved my problem of sorting dates.

Answer 3

Try:

drop table IF EXISTS #test
CREATE TABLE #Test(
[id] [int] NULL,
[data] [date] NULL,
[field1] [float] NULL,
[field2] [float] NULL,
[field3] [float] NULL,
[field4] [float] NULL
) ON [PRIMARY]
GO


INSERT INTO #Test
(id, data, field1, field2, field3, field4)
VALUES (1, CONVERT(DATETIME, '1955-01-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(1, CONVERT(DATETIME, '2022-01-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(2, CONVERT(DATETIME, '2022-02-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(3, CONVERT(DATETIME, '2022-03-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(4, CONVERT(DATETIME, '2022-04-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(5, CONVERT(DATETIME, '2022-05-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(6, CONVERT(DATETIME, '2022-06-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(7, CONVERT(DATETIME, '2022-07-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)
,(8, CONVERT(DATETIME, '2022-08-16 00:00:00', 102), 2.3, 3.2, 4.5, 6.8)

DECLARE @SQL NVARCHAR(MAX), @cols NVARCHAR(MAX)

SELECT @cols = STRING_AGG(QUOTENAME(CONVERT(VARCHAR(10),data, 23)), ',') FROM #Test;
SET @sql = ';WITH cte AS (SELECT Id, [data], FieldName, fieldValue
FROM #Test t CROSS APPLY (VALUES (''field1'', field1), (''field2'', field2), (''field3'', field3), (''field4'', field4)) F (fieldName, fieldValue))

SELECT * FROM (select [Data], FieldName, FieldValue from cte) X PIVOT (max(fieldValue) for [data] in (' + @cols + ')) pvt'

--;WITH cte AS (SELECT Id, [data], FieldName, fieldValue
--FROM #Test t CROSS APPLY (VALUES ('field1', field1), ('field2', field2), ('field3', field3), ('field4', field4)) F (fieldName, fieldValue))

--SELECT * FROM (select [Data], FieldName, FieldValue from cte) X PIVOT (max(fieldValue) for [data] in ([1955-01-16],[2022-01-16],[2022-02-16],[2022-03-16],[2022-04-16],[2022-05-16],[2022-06-16],[2022-07-16],[2022-08-16])) pvt

execute (@SQL);

maurizio verdirame 41 Reputation points

2022-05-14T10:34:58.243+00:00

thanks the information was very useful,
solved my other problem of sorting dates.

Share via

PivotTable with column dates

3 additional answers

Your answer