将两行转换为一行

Question

我们有一个审计系统，它记录所有系统表中发生的所有更改。基本上，这就是AuditLog表的样子：

​

​

目前我正在创建几个sql视图来查询不同类型的信息。除了一点以外，一切都很好。如果您再次查看该图，您将看到我有一个SubscriptionMetadata表，它是一个包含2个字段(MetaName和MetaValue)的键-值对表。图像显示的是订阅有一个新的水印，它的值是'Licensed : Fullname，Company，V...‘。

在我看来，我需要的是将这两行转换为具有以下形式的一行：

41 - Insert - SubscriptionMetadata - 2012-10-19 - 53DA4XXXXXX - Watermark -  Licensed copy: Fullname, Company, V...

我真的无法想象我怎么才能做到这一点，也不能搜索到它。

还有另一个问题(我认为)，这些行总是按这个顺序出现:首先是MetaName，然后是MetaValue。这是唯一能知道他们有血缘关系的方法。

你能帮帮我吗？

Answer 1

虽然我看不到您的整个表结构，但您可以通过以下方式转换数据。这两种解决方案都会将数据放在单独的列中：

;with data(id, [action], [type], [date], [col], metatype, value) as
(
    select 41, 'Insert', 'SubscriptionMetaData', '2012-10-19', '53DA4XXX','Metaname', 'Watermark'
    union all
    select 41, 'Insert', 'SubscriptionMetaData', '2012-10-19', '53DA4XXX','MetaValue', 'Licensed copy: Fullname, Company'
) 
select id, action, type, date, col,
    MAX(case when metatype = 'Metaname' then value end) Name,
    MAX(case when metatype = 'MetaValue' then value end) Value
from data
group by id, action, type, date, col

请参阅SQL Fiddle with Demo

或者，您可以对数据使用PIVOT来获得相同的结果：

;with data(id, [action], [type], [date], [col], metatype, value) as
(
    select 41, 'Insert', 'SubscriptionMetaData', '2012-10-19', '53DA4XXX','Metaname', 'Watermark'
    union all
    select 41, 'Insert', 'SubscriptionMetaData', '2012-10-19', '53DA4XXX','MetaValue', 'Licensed copy: Fullname, Company'
) 
select *
from
(
    select id, [action], [type], [date], [col], metatype, value
    from data
) src
pivot
(
    max(value)
    for metatype in (Metaname, MetaValue)
) piv

请参阅SQL Fiddle with Demo

两者产生相同的结果：

| ID | ACTION |                 TYPE |       DATE |      COL |      NAME |                            VALUE |
-------------------------------------------------------------------------------------------------------------
| 41 | Insert | SubscriptionMetaData | 2012-10-19 | 53DA4XXX | Watermark | Licensed copy: Fullname, Company |

Answer 2

您可以使用coalesce函数通过存储过程或标量值函数完成此操作，如下所示：

DECLARE @Results NVARCHAR(MAX);
DECLARE @Token NVARCHAR(5) = '-'; -- separator token
SELECT @Results = coalesce(@Results + @Token,'') + t.MetaValue from (select * from TableName where MetaName = 'SubscriptionMetadata') t;
RETURN @Results; -- variable containing the concatenated values

Answer 3

Here is a working example.请根据需要替换列名。Col3 = your string concatenating column。

SELECT t1.col1,t1.col2,
       NameValue =REPLACE( (SELECT col3 AS [data()]
           FROM mytable t2
           WHERE t2.col1 = t1.col1
           ORDER BY t2.col1
           FOR XML PATH('')
           ), ' ', ' : ')
FROM mytable t1
GROUP BY col1,col2 ;

--DATA
1 |  X |  Name
1 |  X |  Value

--RESULTS
1 |  X |  Name:Value --Name Value pair here

编辑:如果您不需要连接(根据您的评论)

SELECT t1.col1, t1.col2, t1.col3 NameColumn, t2.col3 ValueColumn
FROM (SELECT * FROM myTable WHERE col3 = 'Watermark') t1 JOIN 
     (SELECT * FROM myTable WHERE NOT (col3 = 'Watermark')) t2 
     ON t1.col1 = t2.col1