Excel or SQL Vertical data to Horizontally

Question

I have data extract from SQL Server about 5000 rows as shown below. And I copy-paste it into an Excel file.

But I want to convert data to this format:

Should I complete this job at the first step (SQL Server side) or in Excel? Which solution is more easier to investigate and learn?

Also I would be glad if you could give an example.

Thanks.

Answer 1

If you have a known or maximum number of columns you can use PIVOT in concert with row_number()

If the maximum is unknown, you would need Dynamic SQL

Example or dbFiddle

Select *
 From  (
        Select A.ID
              ,B.* 
         from (Select *
                     ,Grp = row_number() over (partition by ID order by ID) 
                From  YourTable
              ) A
         Cross Apply ( values (concat('Column1-',Grp),Column1)
                             ,(concat('Column2-',Grp),Column2)
                             ,(concat('Column3-',Grp),Column3)
                             ,(concat('Column4-',Grp),Column4)
                     ) B(Col,Val)
       ) src
 Pivot (max(Val) for Col in ( [Column1-1]
                             ,[Column2-1]
                             ,[Column3-1]
                             ,[Column4-1]
                             ,[Column1-2]
                             ,[Column2-2]
                             ,[Column3-2]
                             ,[Column4-2]
                             ,[Column1-3]
                             ,[Column2-3]
                             ,[Column3-3]
                             ,[Column4-3]
                            )  ) pvt 

Results

EDIT - Update for Dynamic SQL and Variable Datatype

Declare @SQL varchar(max) = (
Select string_agg(concat('[',Col,N,']'),',') within group (order by N,Col)
 From (values ('Column1-')
             ,('Column2-')
             ,('Column3-')
             ,('Column4-')
      ) A(Col)
Cross Join ( Select distinct N=row_number() over (partition by ID order by ID) From  YourTable ) B
)

Set @SQL = '
Select *
 From  (Select A.ID
              ,B.* 
         from (Select *
                     ,Grp = row_number() over (partition by ID order by ID) 
                From  YourTable
              ) A
         Cross Apply ( Select col = concat([Key],''-'',Grp)
                             ,Val = value
                        From  OpenJson((Select A.* For JSON Path,Without_Array_Wrapper )) 
                     ) B
       ) src
 Pivot (max(Val) for Col in ( '+@SQL+' )  ) pvt '

 Exec(@SQL)