StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to make denormalized table from normalized table for OLAP Database (PIVOT-UNPIVOT)
text
Body
copied!<p><strong>My problem is how to create denormalized (OLAP) table from normalized table.</strong></p> <p>I have OLTP database with some data. See the chart below:</p> <p><img src="https://i.stack.imgur.com/pUExn.png" alt="OLTP_database diagramm"></p> <p>Pay particular attention to the tables "Variable", "Variable_Value" and "Variable_Sample".</p> <p><strong>Variable:</strong></p> <p><img src="https://i.stack.imgur.com/NVPIy.png" alt="Variable"></p> <p><strong>Variable_Value:</strong></p> <p><img src="https://i.stack.imgur.com/QCrQ3.png" alt="enter image description here"></p> <p><strong>Variable_Sample:</strong></p> <p><img src="https://i.stack.imgur.com/8bAq5.png" alt="enter image description here"></p> <p>The script that creates the table "Variable_Sample" see below:</p> <pre><code> ALTER PROCEDURE [dbo].[VarSample] AS BEGIN truncate table UIR_DB.dbo.Variable_Sample Declare params cursor for Select distinct id_variable from UIR_DB.dbo.Variable_Value open params Declare @idparam int Declare @csql nvarchar(max) = '' Declare @csql2 nvarchar(max) = '' Declare @i int = 1 fetch next from params into @idparam while @@FETCH_STATUS = 0 begin Select @csql2 = @csql2 + ', param' +LTrim(Str(@i)) Select @csql = @csql + ' (Select value as param'+LTrim(Str(@i))+' from UIR_DB.dbo.Variable_Value where id_variable = '+LTrim(Str(@idparam))+') a'+LTrim(Str(@i))+' cross join' Set @i = @i+1 fetch next from params into @idparam end Select @csql = ' Insert INTO UIR_DB.dbo.Variable_Sample SELECT id, CAST(SubString(Param,6,LEN(Param)-5) as int) param_id, param_val FROM (Select ROW_Number() over(order by '+SubString(@csql2,2,LEN(@csql2)-1)+') id, '+SubString(@csql2,2,LEN(@csql2)-1)+' from '+SubString(@csql,1,LEN(@csql)-11) + ') p UNPIVOT (param_val FOR Param IN ('+SubString(@csql2,2,LEN(@csql2)-1)+') )AS unpvt ' print @csql exec sp_executesql @csql delete from Sample Insert into Sample select distinct UIR_DB.dbo.Variable_Sample.idsample from UIR_DB.dbo.Variable_Sample close params deallocate params END </code></pre> <p>I need to create table, something like "Variable_Sample_Olap" following form: </p> <p><strong>Variable_Sample_Olap:</strong></p> <p><img src="https://i.stack.imgur.com/iuZPe.png" alt="enter image description here"></p> <p>(Of course, if it would be variable3, ..., variableN, then it would be N columns in "Variable_Sample_Olap".)</p> <p><strong>Can someone help me with script, that transforms Variable_Sample in Variable_Sample_Olap?</strong></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload