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.
CodePudding user response：
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 
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)