将行值设为列值SQL PIVOT

SQL Returns the following results from table

SQL从表中返回以下结果

+-----+----+-----+----+----+
| PID | PY | QTY | TS | TM |
+-----+----+-----+----+----+
|  99 | CT |   1 |  1 |  6 |
|  99 | E  |   3 |  1 |  5 |
|  99 | GR |   4 |  1 |  6 |
+-----+----+-----+----+----+

I would like to use PIVOT (if even possible) to make the results like so:

我想使用PIVOT(如果可能的话)来产生如下结果:

+-----+----+-----+----+----+----+------+------+------+------+------+------+------+
| PID | PY | QTY | TS | TM | PY | QTY  |  TS  |  TM  |  PY  | QTY  |  TS  |  TM  |
+-----+----+-----+----+----+----+------+------+------+------+------+------+------+
|  99 | CT |   1 |  1 |  6 | E    | 3    | 1    | 5    | GR   | 4    | 1    | 6    |
| 100 | V  |   6 |  6 |  2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+-----+----+-----+----+----+----+------+------+------+------+------+------+------+

1 个解决方案

#1

If you have a limited number of values, then the easiest way to get the result would be to use an aggregate function with some CASE expressions.

如果您的值有限,那么获得结果的最简单方法是使用带有一些CASE表达式的聚合函数。

select 
  pid,
  PY1 = max(case when py = 'CT' then PY end),
  QTY1 = max(case when py = 'CT' then QTY else 0 end),
  TS1 = max(case when py = 'CT' then TS else 0 end),
  TM1 = max(case when py = 'CT' then TM else 0 end),
  PY2 = max(case when py = 'E' then PY end),
  QTY2 = max(case when py = 'E' then QTY else 0 end),
  TS2 = max(case when py = 'E' then TS else 0 end),
  TM2 = max(case when py = 'E' then TM else 0 end),
  PY3 = max(case when py = 'GR' then PY end),
  QTY3 = max(case when py = 'GR' then QTY else 0 end),
  TS3 = max(case when py = 'GR' then TS else 0 end),
  TM3 = max(case when py = 'GR' then TM else 0 end),
  PY4 = max(case when py = 'V' then PY end),
  QTY4 = max(case when py = 'V' then QTY else 0 end),
  TS4 = max(case when py = 'V' then TS else 0 end),
  TM4 = max(case when py = 'V' then TM else 0 end)
from yourtable
group by pid;

See SQL Fiddle with Demo. You can use PIVOT to get the result but it will be messier because you'll need to unpivot the multiple columns first, and then pivot them. If you want to use the pivot function, then you'd have to do the following.

请参阅SQL Fiddle with Demo。您可以使用PIVOT来获得结果,但它会更加混乱,因为您需要首先取消多列的转换,然后再转动它们。如果要使用pivot功能,则必须执行以下操作。

First, use row_number() to assign a unique value to each pid combination:

首先,使用row_number()为每个pid组合分配一个唯一值:

select pid, py, qty, ts, tm,
  rn = row_number() over(partition by pid order by pid)
from yourtable

See Demo

Then unpivot the multiple columns py, qty, ts and tm:

然后将多列py,qty,ts和tm取消忽略:

select 
  pid, 
  new_col = col + cast(rn as varchar(10)),  
  val
from
(
  select pid, py, qty, ts, tm,
    rn = row_number() over(partition by pid order by pid)
  from yourtable
) d
cross apply
(
  select 'py', py union all
  select 'qty', cast(qty as varchar(10)) union all
  select 'ts', cast(ts as varchar(10)) union all
  select 'tm', cast(tm as varchar(10))
) c (col, val)

See Demo

I used CROSS APPLY to convert the multiple columns into multiple rows. Finally, you'll pivot the new_col and their corresponding values:

我使用CROSS APPLY将多列转换为多行。最后,您将转动new_col及其相应的值:

select pid,
    py1, qty1, ts1, tm1, py2, qty2, ts2, tm2,
    py3, qty3, ts3, tm3, py4, qty4, ts4, tm4
from
(
  select 
    pid, 
    new_col = col + cast(rn as varchar(10)),  
    val
  from
  (
    select pid, py, qty, ts, tm,
      rn = row_number() over(partition by pid order by pid)
    from yourtable
  ) d
  cross apply
  (
    select 'py', py union all
    select 'qty', cast(qty as varchar(10)) union all
    select 'ts', cast(ts as varchar(10)) union all
    select 'tm', cast(tm as varchar(10))
  ) c (col, val)
) src
pivot
(
    max(val)
    for new_col in (py1, qty1, ts1, tm1, py2, qty2, ts2, tm2,
                    py3, qty3, ts3, tm3, py4, qty4, ts4, tm4)
) piv;

See Demo

Both versions will give the same final result.

两个版本都会给出相同的最终结果。

| pid | py1 | qty1 | ts1 | tm1 | py2 | qty2 | ts2 | tm2 | py3 | qty3 | ts3 | tm3 |    py4 |   qty4 |    ts4 |    tm4 |
|-----|-----|------|-----|-----|-----|------|-----|-----|-----|------|-----|-----|--------|--------|--------|--------|
|  99 |  CT |    1 |   1 |   6 |   E |    3 |   1 |   5 |  GR |    4 |   1 |   6 | (null) | (null) | (null) | (null) |