SQL script for special data representation

Question

Please help me with my problem of data representation.

I have some SQL script, that creates the following data presentation:

Declare params cursor for 
Select distinct id_variable from [UIR_DB].[dbo].[Variable_Values]
open params
Declare @idparam int
Declare @csql nvarchar(max)  = ''
Declare @csql2 nvarchar(max)  = ''
declare @csq13 nvarchar(max) = ''
Declare @i int = 1
fetch next from params into @idparam
while @@FETCH_STATUS = 0
begin
Select @csql2 = @csql2 +', id_var'+LTRIM(Str(@i))+ ', param' +LTrim(Str(@i))
Select @csql =  ' (Select id_variable as id_var'+LTrim(Str(@i))+', value as param'+LTrim(Str(@i))+' from
[UIR_DB].[dbo].[Variable_Values] where id_variable = '+LTrim(Str(@idparam))+') a'+LTrim(Str(@i))+'
cross join'+@csql
Set @i = @i+1
fetch next from params into @idparam
end
Select @csql = 'Select '+SubString(@csql2,2,LEN(@csql2)-1)+' from
'+SubString(@csql,1,LEN(@csql)-11)+'order by'+SubString(@csql2,2,LEN(@csql2)-1)
print @csql
exec sp_executesql @csql
close params
deallocate params

execute Script 1

My knowledge of SQL not so far, so please, if it wouldn't be a big problem, i need script, that will creates the next data representation from Script1 results: New script results

Anvesh · Answer 1 · 2013-04-24T11:53:51.230

0

Try this:

you can add your first result set into one temp table and perform following query.

you can add alias or id as per you requriment.

SELECT id_var1,Param1 FROM [UIR_DB].[dbo].[Variable_Values]
UNION
SELECT id_var2,Param2 FROM [UIR_DB].[dbo].[Variable_Values]

edited Apr 24 '13 at 11:53

answered Apr 24 '13 at 11:48

Anvesh

7,103
3
45
43

it should be a dinamic sql query, because it can be more, that variable1 and variable2. – George Pirkulov Nov 06 '13 at 08:27

score 0 · Answer 2 · answered Apr 24 '13 at 12:17

Hope this helps you. This can be done in 2 steps

1) Generate auto increment number for records in result set

WITH CTE
AS
(
  SELECT ROW_NUMBER() OVER(ORDER BY id_var1) AS id_sample,
  [id_var1], [param1],[id_var2], [param2]
  FROM Table1
)

Note : you can add incremental number in your code, the above is just example

2) Use below query to get desired result

SELECT 
c.id_sample,
c.id_variable
,c.value
FROM CTE
CROSS APPLY
(
  VALUES
  (id_sample,id_var1,param1),
  (id_sample,id_var2,param2)
)c(id_sample,id_variable,value)

SQLFiddle Demo

it should be dinamic sql query, because it can be more, that variable1 and variable2. — George Pirkulov, Nov 06 '13 at 08:19

SQL script for special data representation

2 Answers2