将表格格式更改为透视

Question

在本期续篇中

主代码：

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)

然后我需要得到一个最接近给定日期的值列表：

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

然后弄一张这样的桌子：

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

但如何才能得到这种格式的结果：

    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

我在此上下文中使用pivot的所有尝试都没有成功。

更新

根据@Gordon Linoff和@xxx的建议，尝试重做使用Dinamic SQL的代码，现在开始：

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 = 

‘op为（选择op.id_object,op.id_param,name,op.cdate,op.value,ROW_NUMBER()OVER（分区按op.id_object,op.id_param顺序按op.cdate DESC）为seqnum FROM objects_params opjoin params p ON op.id_param=p.id_param

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

选择id_object,'+@names+'（选择id_object,value,name FROM op,其中seqnum=1)作为tblhtml" target="_blank">数据透视(('+@names+')中name的max（value））PIV

execute(@SQL)

搞定了。

Answer 1

您可以使用条件聚合：

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;