Change table format to pivot

Question

In the continuation of this issue

Main code:

CREATE TABLE params
(
    id_param smallint PRIMARY KEY,
    name varchar(50) NOT NULL
)

CREATE TABLE objects_params
(
    id_object int,
    id_param smallint NOT NULL,
    cdate smalldatetime,
    value int
)

INSERT INTO dbo.params (id_param, name)
VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), 
       (5, 'e'), (6, 'f'), (7, 'g')

INSERT INTO dbo.objects_params (id_object, id_param, cdate, value)
VALUES (1, 1, '20191206',NULL), (1, 2,'20191212', 100), (1, 1, '20191201', 110),
       (2, 4, '20191211',120), (2, 1,'20190101', 130), (2, 3, '20191212', 140),
       (2, 4, '20191111',150), (2, 3,'20190201', 160), (2, 3, '20190312', 170),
       (2, 3, '20191201', 175),(2, 3, '20191202', 180), (2, 3, '20191203', 185),
       (2, 3, '20191204', 190)

Then I need to get a list of values closest to the given date:

DECLARE @userdate DATETIME
SET @userdate='20191202'

DECLARE @names as VARCHAR(MAX)
SELECT @names =
COALESCE(@names + ', ','') + QUOTENAME(name)
FROM
   (SELECT distinct name
    FROM objects_params 
    JOIN params ON objects_params.id_param = params.id_param
   ) AS B;

With t_sql as ( 
    SELECT  id_object, objects_params.id_param, name, cdate, value  
    FROM objects_params 
    JOIN params ON objects_params.id_param = params.id_param)
    --Where value <> ''

SELECT id_object, id_param, name, cdate, value  
FROM 
    (
        SELECT RANK() OVER (PARTITION BY id_object, id_param ORDER BY abs(datediff(ss, @userdate,cdate)) ASC) AS DateRank, *
        FROM t_sql
        WHERE cdate < @userdate
    ) 
AS DetailsRanking
WHERE DetailsRanking.DateRank=1

and get a table like this:

id_object id_param   name      cdate      value
-----------------------------------------------
   1         1         a     2019-12-01    110
   2         1         a     2019-01-01    130
   2         3         c     2019-12-01    175
   2         4         d     2019-11-11    150

But how can I get the results in this format:

    id_object     a         b       c      d      e      f      g   
    ---------------------------------------------------------------
       1         110      null    null   null   null   null   null
       2         130      null    175    150    null   null   null

All my attempts to use pivot in this context were unsuccessful.

Update

According to advice of @Gordon Linoff and @xXx tried to redo the code for using Dinamic SQL, so here we go:

USE [DConturDb]
GO

DECLARE @userdate VARCHAR(MAX)
SET @userdate='20191202';

DECLARE @names as VARCHAR(MAX)
SELECT @names =
COALESCE(@names + ', ','') + QUOTENAME(name)
FROM
   (SELECT name
    FROM params 
   ) AS B;

DECLARE @SQL as VARCHAR(MAX)
SET @SQL =

'WITH op as ( SELECT op.id_object, op.id_param, name, op.cdate, op.value, ROW_NUMBER() OVER (PARTITION BY op.id_object, op.id_param ORDER BY op.cdate DESC) as seqnum FROM objects_params op JOIN params p ON op.id_param = p.id_param

  WHERE op.cdate <='''+ @userdate +'''
 )

SELECT id_object, ' + @names + ' FROM ( select id_object, value, name from op where seqnum=1 ) as tbl pivot ( max(value) for name in (' + @names + ') ) piv'

execute(@SQL)

Done.

Does this answer your question? [how to make your data horizontal](https://stackoverflow.com/questions/58605804/how-to-make-your-data-horizontal) — Amira Bedhiafi, Jan 19 '20 at 16:30
The question is similar, but, as I said in your answer, I would like to understand how to use it in the context of this example — heso, Jan 19 '20 at 18:31

score 2 · Answer 1 · answered Jan 19 '20 at 16:15

2

You can use conditional aggregation:

WITH op as ( 
      SELECT op.id_object, op.id_param, name, op.cdate, op.value,
             ROW_NUMBER() OVER (PARTITION BY op.id_object, op.id_param ORDER BY op.cdate DESC) as seqnum
      FROM objects_params op JOIN
           params p
           ON op.id_param = p.id_param
      WHERE op.cdate < @userdate
     )
SELECT id_object,
       MAX(CASE WHEN id_param = 1 THEN value END) as value_1,  
       MAX(CASE WHEN id_param = 2 THEN value END) as value_2, 
       . . .  
FROM op
WHERE seqnum = 1
GROUP BY id_object;

answered Jan 19 '20 at 16:15

Gordon Linoff

1,242,037
58
646
786

Works great, thanks. But is it possible not to explicitly indicate the names of the columns, but to make sure that they are automatically set? Is it possible to use the range “@names” that is indicated in my example? – heso Jan 19 '20 at 16:33
@heso . . . You would need to use dynamic SQL if you want the names to be anything other than constants. – Gordon Linoff Jan 19 '20 at 18:27

score 1 · Answer 2 · answered Jan 27 '20 at 06:47

You will get your wish result.

DECLARE @userdate DATETIME
   SET @userdate='20191202'
   select * from (
   select b.id_object,value,a.name from params a
   INNER JOIN objects_params b on a.id_param = b.id_param
   where cdate < @userdate--here is your parameters
   ) as t 
   pivot (sum(value) for name IN ([a],[b],[c],[d],[e],[f],[g]) ) as Pivot_tbl --pivot based on names

score 0 · Answer 3 · answered Jan 19 '20 at 16:29

SQL Fiddle - Just replace getdate() by the variable you are using :

MS SQL Server 2017 Schema Setup:

CREATE TABLE Result (id_object int,id_param int,name varchar(255)
                    ,cdate date,  val int);
INSERT INTO  Result(id_object,id_param,name,cdate,val)
            VALUES (1,1,'a','2019-12-01',110)
            ,(2,1,'a','2019-01-01',130)
            ,(2,3,'c','2019-12-01',175)
            ,(2,4,'d','2019-11-11',150)

Query 1:

with CTE AS (select *,
(CASE WHEN name='a' THEN val  END) AS a,
(CASE WHEN name = 'b' THEN val END) AS b,
(CASE WHEN name='c' THEN val END) AS c,
(CASE WHEN name='d' THEN val END) AS d,
(CASE WHEN name='e' THEN val END) AS e,
(CASE WHEN name='f' THEN val END) AS f,
(CASE WHEN name='g' THEN val END) AS g,             
ROW_NUMBER() OVER (PARTITION BY id_param,id_object Order By cdate) as rn
from Result
             where cdate < getdate()
group by id_object,id_param,name,cdate,val              )

select c.id_object

,max(c.a) AS a
,max(c.b) AS b
,max(c.c) AS c
,max(c.d) AS d
,max(c.e) AS e
,max(c.f) AS f
,max(c.g) AS g

from cte c
where rn=1
group by c.id_object

Results:

| id_object |   a |      b |      c |      d |      e |      f |      g |
|-----------|-----|--------|--------|--------|--------|--------|--------|
|         1 | 110 | (null) | (null) | (null) | (null) | (null) | (null) |
|         2 | 130 | (null) |    175 |    150 | (null) | (null) | (null) |

thanks, But I do not have a separate table "Results". Tried to combine your and my code (in query field) [mycode](http://sqlfiddle.com/#!9/11f05b/1) Bur there is an error: "Incorrect syntax near the keyword "where"." I would also like to understand whether it is possible not to explicitly indicate the column names — heso, Jan 19 '20 at 18:28
@heso please check how to use variables with sql fiddle. Also what do you mean by not to explicitly indicate the column names? — Amira Bedhiafi, Jan 19 '20 at 18:46
I used this site (sql fiddle) only as an example of my code, because I can’t insert it here in the comments. "not to explicitly indicate the column names" - I mean that we have the names "a-g" in this example, but in another, there may be others. So is it possible not to specify names directly, for example, to form a separate array from them (as in my example @names) - and use it. — heso, Jan 19 '20 at 18:59
@heso please update your question. https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — Amira Bedhiafi, Jan 19 '20 at 19:00

Change table format to pivot

3 Answers3