SQL Pivot Multiple Rows and Columns to Single Row

Question

I am trying to write a query that pivots multiple rows and columns into a single easy to read columns. This is what the source data looks like.

Time	Source	Temp1	Temp 2	Temp 3	Temp 4
1	1	10	11	12	13
1	2	20	21	22	23
1	3	30	31	32	33
1	4	40	41	42	43

This is what I want

Time	Src1_Temp1	Src1_Temp 2	Src1_Temp 3	Src1_Temp 4	Src2_Temp1	Src2_Temp 2	Src2_Temp 3	Src2_Temp 4
1	10	11	12	13	20	21	21	23

I am attempting to use a Pivot in SQL, however, I am having a hard time defining multiple aggregates within the pivot

Select Time,
       [1],
       [2],
       [3],
       [4],
FROM 
(
  SELECT Source
      , Time
      ,Temp1
      ,Temp2
      ,Temp2
      ,Temp3
  FROM MySourceTable
  group by Time, Source, Temp1, Temp2, Temp3, Temp4
) as SourceTable
PIVOT
(
    MAX(Temp1) 
    FOR Source in ([1] Src1, [2] Src2, [3] Src3, [4] Src4)  
) as Pivot

The issue I have is, I cannot select Max(Temp1), Max(Temp2), Max(Temp3), Max(Temp4) in the PIVOT.

Any help would be greatly appreciated

https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms — Reporter, May 19 '23 at 13:46
Does this answer your question? [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — lemon, May 19 '23 at 13:48
It does not. That is using dynamic sql and is taking mutliple rows with a single column of data. I have multiple Rows and Multiple columns — JoeyD, May 19 '23 at 14:30
This was literally answered like 5 questions ago: https://stackoverflow.com/questions/76289239/convert-column-value-data-to-row-value-data-in-sql — siggemannen, May 19 '23 at 15:37
But it really wasnt. If you look at the answer, it is only pivoting one column from the source table. Even the Dynamic SQL is pulling 1 column. I am asking how i can pull multiple columns from the source table. — JoeyD, May 19 '23 at 17:16

John Cappelletti · Accepted Answer · 2023-05-19T17:35:25.903

First UNPIVOT your data, then it becomes a small matter to PIVOT

Example

Select *
 From  (
        Select A.Time
              ,B.*
         From  YourTable A
         Cross Apply ( values ( concat('Src',source,'_Temp 1'),[Temp 1])
                             ,( concat('Src',source,'_Temp 2'),[Temp 2])
                             ,( concat('Src',source,'_Temp 3'),[Temp 3])
                             ,( concat('Src',source,'_Temp 4'),[Temp 4])
                     ) B(Item,Value)
       ) src
 Pivot ( max(value) for Item in (
                                    [Src1_Temp 1],
                                    [Src1_Temp 2],
                                    [Src1_Temp 3],
                                    [Src1_Temp 4],
                                    [Src2_Temp 1],
                                    [Src2_Temp 2],
                                    [Src2_Temp 3],
                                    [Src2_Temp 4],
                                    [Src3_Temp 1],
                                    [Src3_Temp 2],
                                    [Src3_Temp 3],
                                    [Src3_Temp 4],
                                    [Src4_Temp 1],
                                    [Src4_Temp 2],
                                    [Src4_Temp 3],
                                    [Src4_Temp 4]
                                ) ) src

Results

SQL Pivot Multiple Rows and Columns to Single Row

1 Answers1