具有自定义值的数据透视表

Question

我有一个表，比如FIDDLE HERE

+----+------+------+-----+-----+
| id | year | sell | buy | own |
+----+------+------+-----+-----+
| 1  | 2016 | 9    | 2   | 10  |
| 1  | 2017 | 9    |     | 10  |
| 1  | 2018 |      | 2   | 10  |
| 2  | 2016 | 7    | 2   | 11  |
| 2  | 2017 | 2    |     |     |
| 2  | 2018 |      |     | 18  |
+----+------+------+-----+-----+

create table test(id varchar(20), year varchar(20), 
sell varchar(20), buy varchar(20), 
own varchar(20));
insert into test values('1', '2016','9','2','10' )
insert into test values('1', '2017','9',NULL,'10' )
insert into test values('1', '2018',NULL,'2','10' )
insert into test values('2', '2016','7','2','11' )
insert into test values('2', '2017','2',NULL,'17' )
insert into test values('2', '2018','5','2','18' )

我正在尝试PIVOT，但我不想聚合值，而是想保留一些字母，如果它不是null(S-Sell，B-Buy，O-Own(。如果有特定年份的所有列的值，那么我需要该年份的S_B_O。如果只有卖出和买入的值，则S_B等，因此预期输出为

+----+-------+------+------+
| ID | 2016  | 2017 | 2018 |
+----+-------+------+------+
| 1  | S_B_O | S_O  | B_O  |
+----+-------+------+------+
| 2  | S_B_O | S    | O    |
+----+-------+------+------+

我得到的最接近的是使用条件聚合(MAX和concat(而不是PIVOT，但如果任何一个是NULL，这也会给出null。请提出解决方案。

select ID, 
MAX(CASE WHEN Year = '2016' AND sell is not null THEN 'S_' END + 
CASE WHEN Year = '2016' AND buy is not null THEN 'B_' END +
CASE WHEN Year = '2016' AND own is not null THEN 'O' END)
AS [2016],
MAX(CASE WHEN Year = '2017' AND sell is not null THEN 'S_' END + 
CASE WHEN Year = '2017' AND buy is not null THEN 'B_' END +
CASE WHEN Year = '2017' AND own is not null THEN 'O' END)
AS [2017]
/* ......for all year */
from test
group by id 

小提琴在这里

Answer 1

您可以使用CONCAT函数，它将自动处理NULL。

select ID, 
CONCAT(MAX(CASE WHEN Year = '2016' AND sell is not null THEN 'S_' END) , 
MAX(CASE WHEN Year = '2016' AND buy is not null THEN 'B_' END) ,
MAX(CASE WHEN Year = '2016' AND buy is not null THEN 'O' END))
AS [2016],
CONCAT(MAX(CASE WHEN Year = '2017' AND sell is not null THEN 'S_' END) , 
MAX(CASE WHEN Year = '2017' AND buy is not null THEN 'B_' END) ,
MAX(CASE WHEN Year = '2017' AND buy is not null THEN 'O' END))
AS [2017]
from test
group by id

+----+-------+------+
| ID | 2016  | 2017 |
+----+-------+------+
|  1 | S_B_O | S_   |
|  2 | S_B_O | S_   |
+----+-------+------+

更新动态查询。正如@Larnu所说，你应该把这个问题单独提出来。你不应该改变要求。

DECLARE @lst_Years NVARCHAR(MAX) , @query NVARCHAR(MAX)
SET @lst_Years = STUFF((SELECT distinct ',' + QUOTENAME([Year]) 
FROM test 
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')
SET @query = 'SELECT * FROM
(
select ID, [Year],
CONCAT(MAX(CASE WHEN  sell is not null THEN ''S_'' END) , 
MAX(CASE WHEN  buy is not null THEN ''B_'' END) ,
MAX(CASE WHEN buy is not null THEN ''O'' END))
AS [Value]
from test
group by id, [year]) as t
pivot
(
max(value) FOR YEAR IN (' + @lst_Years + ')
) as pvt'
EXEC(@query)

+----+-------+------+-------+
| ID | 2016  | 2017 | 2018  |
+----+-------+------+-------+
|  1 | S_B_O | S_   | B_O   |
|  2 | S_B_O | S_   | S_B_O |
+----+-------+------+-------+