Can you help me with this problem? I have a one table which records measuring data with information related to the ParentID. Unfortunately, each measuring data are written into one column (identified by value of previous columns). I would like to read all values from this column and write it into one row but different columns to have one row about each measuring. Combinations of TagID and Instance values can be 21. So I need in the result 21 columns with value in one row (M1....M21). Running on SQL server 2016.

Source table:

Requested table:

Empty columns could be 0 or rather NULL.

EDIT - another column in Source table:

Requested table:

CodePudding user response：

you are looking for a PIVOT query like below

select ParentID, [14-2010] [M1], [18-3020] [M2],[16-2010] [M3]
from
(
select 
 ParentID, 
 CAST(TagID as NVARCHAR)   "-"  CAST(Instance AS NVARCHAR) as pivotcol,
 Data
from your table )src
PIVOT( MAX(Data) FOR pivotcol in ([14-2010],[18-3020],[16-2010]))p

I intentionally went upto M3. in your use case feel free to add unique combinations of tagid and instance inside the pivot in sub query part and in the top select part.