表结构问题 - 查询透视/取消透视

Question

>我正在尝试查询表以进行数据转换，但遇到了结构问题。我猜我需要枢轴然后取消枢轴，但我不确定从哪里开始。

这是我当前的表

+----+-----------+-------+---------+
| ID |   field   | value |  date   |
+----+-----------+-------+---------+
|  1 | Draw1     | 1500  | NULL    |
|  1 | Draw1Date | NULL  | 4/15/16 |
|  1 | Draw1Fee  | 100   | NULL    |
|  1 | Draw2     | 2000  | NULL    |
|  1 | Draw2Date | NULL  | 3/14/17 |
|  1 | Draw2Fee  | 100   | NULL    |
|  2 | Draw1     | 800   | NULL    |
|  2 | Draw1Date | NULL  | 4/16/18 |
|  2 | Draw1Fee  | 150   | NULL    |
|  2 | Draw2     | 760   | NULL    |
|  2 | Draw2Date | NULL  | 5/6/18  |
|  2 | Draw2Fee  | 150   | NULL    |
+----+-----------+-------+---------+

需要结果

+----+-------+---------+---------+------+
| ID | Draws |  Amount |  Date   |  Fee |
+----+-------+---------+---------+------+
|  1 | Draw1 |    1500 | 4/15/16 |  100 |
|  1 | Draw2 |    2000 | 3/14/17 |  100 |
|  2 | Draw1 |     800 | 4/16/18 |  150 |
|  2 | Draw2 |     760 | 5/6/18  |  150 |
+----+-------+---------+---------+------+

Answer 1

我的答案适用于提供的数据。 如果您正在寻找更通用的解决方案，对于更多种类的数据，您可以尝试找到另一个答案。我没有使用过 PIVOT/UNPIVOT。

测试数据：

create table #t (ID int, field varchar(20), [value] int, [date] date)
insert into #t values 
(1 ,'Draw1'     , 1500  , NULL     ),
(1 ,'Draw1Date' , NULL  , '4/15/16'),
(1 ,'Draw1Fee'  , 100   , NULL     ),
(1 ,'Draw2'     , 2000  , NULL     ),
(1 ,'Draw2Date' , NULL  , '3/14/17'),
(1 ,'Draw2Fee'  , 100   , NULL     ),
(2 ,'Draw1'     , 800   , NULL     ),
(2 ,'Draw1Date' , NULL  , '4/16/18'),
(2 ,'Draw1Fee'  , 150   , NULL     ),
(2 ,'Draw2'     , 760   , NULL     ),
(2 ,'Draw2Date' , NULL  , '5/6/18' ),
(2 ,'Draw2Fee'  , 150   , NULL     )

查询：

;with ct as (
    select ID, field
    from #t
    where field in ('Draw1', 'Draw2')
    group by ID, field
)
select ct.ID, ct.field
, t1.[value] as Amount, t2.[date] as [Date], t3.[value] as Fee
from ct
    inner join #t t1 on t1.ID = ct.ID and t1.field = ct.field
    inner join #t t2 on t2.ID = ct.ID and t2.field = ct.field + 'Date'
    inner join #t t3 on t3.ID = ct.ID and t3.field = ct.field + 'Fee'

结果：

ID  field   Amount  Date        Fee
1   Draw1   1500    2016-04-15  100
1   Draw2   2000    2017-03-14  100
2   Draw1   800     2018-04-16  150
2   Draw2   760     2018-05-06  150

Answer 2

试试这个...

SELECT tblAmount.id         AS ID, 
       tblAmount.field      AS Draws, 
       Max(tblAmount.value) AS Amount, 
       Max(tblDate.[date])  AS [Date], 
       Max(tblFee.value)    AS Fee 
FROM   tablename tblAmount 
       INNER JOIN (SELECT id, field, [date] 
                   FROM   tablename 
                   WHERE  [date] IS NOT NULL AND field LIKE '%Date') tblDate 
               ON tblAmount.id = tblDate.id   
                  AND tblDate.field LIKE tblAmount.field + '%'              
       INNER JOIN (SELECT id, field, value 
                   FROM   tablename 
                   WHERE  value IS NOT NULL AND field LIKE '%Fee') tblFee 
               ON tblAmount.id = tblFee.id                
WHERE  tblAmount.value IS NOT NULL 
       AND tblAmount.field NOT LIKE '%Fee' 
       AND tblAmount.field NOT LIKE '%Date' 
GROUP  BY tblAmount.id, tblAmount.field 
ORDER  BY tblAmount.id, tblAmount.field 

输出

+----+-------+--------+------------+-----+
| ID | Draws | Amount |    Date    | Fee |
+----+-------+--------+------------+-----+
|  1 | Draw1 |   1500 | 2016-04-15 | 100 |
|  1 | Draw2 |   2000 | 2017-03-14 | 100 |
|  2 | Draw1 |    800 | 2018-04-16 | 150 |
|  2 | Draw2 |    760 | 2018-05-06 | 150 |
+----+-------+--------+------------+-----+

演示：http://www.sqlfiddle.com/#!18/97688/101/0

Answer 3

我会简单地做：

select id, left(field, 5),
       max(case when len(field) = 5 then value end) as value,
       max(case when field like '%date' then value end) as date,
       sum(case when field like '%fee' then value end) as fee
from t
group by id, left(field, 5);

如果你的领域真的更复杂，你是否正在寻找date和最后的fee，之前的任何内容，那么使用cross apply：

select t.id, v.draws,
       max(case when t.field = v.draws then value end) as value,
       max(case when t.field like '%date' then value end) as date,
       sum(case when t.field like '%fee' then value end) as fee
from t cross apply
     (values (replace(replace(field, 'date', ''), 'fee', '')) v(draws)
group by id, v.draws;