生成选择查询中的三行结果

Question

我有以下表格

SELECT TableCode, Col1, Col2
FROM TableA
WHERE TableCode = 23

表格结果：

TableCode   |  Col1    |   Col1
23          | CustCode |    QS
23          | CatCode  |    QS

接下来，我编写了一个关于TableA的查询，返回以下结果：

查询语句：

SELECT TableCode,x.ColCode,
       x.ColumnName + '_' + CONVERT(VARCHAR(5), ROW_NUMBER() OVER (PARTITION BY X.COL ORDER BY X.COL)) [ColumnName],X.Values,
       ROW_NUMBER() OVER (PARTITION BY X.COL ORDER BY X.COL) [RowNo]
FROM TableA a CROSS APPLY   
     (SELECT 1 ColCode,'ParaName' ColumnName,Col1 Values
      UNION ALL  
      SELECT 2,'ParaSource',Col2   
     ) x
WHERE TableCode = 23;

结果：

TableCode | ColCode | ColumnName | Values |   RowNo
23       |   1     | ParaName_1 | CustCode |    1
23       |   1     | ParaName_2  | CatCode |    2
23       |     2   | ParaSource_1 | QS     | 1 
23        |   2    | ParaSource_2 | QS     | 2

我需要以下输出：

所需输出：

TableCode | ColCode | ColumnName | Values |   RowNo
23       |   1     | ParaName_1 | CustCode |    1
23       |   1     | ParaName_2  | CatCode |    2
23       |   1     | ParaName_3  | Null |   3
23       |     2   | ParaSource_1 | QS     | 1 
23        |   2    | ParaSource_2 | QS     | 2
23        |   2    | ParaSource_3 | null   | 3

- Vinayak Gurav

Answer 1

利用几个常用表达式和row_number()以及表值构造函数(values (...),(...))来进行cross join操作，将数字1、2和3进行连接，然后使用left join返回每个TableCode的3行数据，即使源表中没有三行数据。

;with numbered as (
  select *, rn = row_number() over (order by (select 1))
  from TableA
  where TableCode = 23
)
, cte as (
  select distinct tc.TableCode, a.Col1, a.Col2, v.rn
  from numbered tc 
    cross join (values (1),(2),(3)) v (rn)
    left join numbered a
      on a.TableCode = tc.TableCode
     and a.rn = v.rn
)
select 
    a.TableCode
  , x.ColCode
  , [ColumnName] = x.ColumnName + '_' + convert(varchar(5),a.rn)
  , X.Value
  ,[RowNo] = a.rn
from cte a
  cross apply (values (1,'ParaName',Col1),(2,'ParaSource',Col2)) 
    as x(ColCode, ColumnName, Value)
order by ColCode, RowNo;

rextester演示：{{link1：http://rextester.com/CJU8986}}

返回：

+-----------+---------+--------------+----------+-------+
| TableCode | ColCode |  ColumnName  |  Value   | RowNo |
+-----------+---------+--------------+----------+-------+
|        23 |       1 | ParaName_1   | CustCode |     1 |
|        23 |       1 | ParaName_2   | CatCode  |     2 |
|        23 |       1 | ParaName_3   | NULL     |     3 |
|        23 |       2 | ParaSource_1 | QS       |     1 |
|        23 |       2 | ParaSource_2 | QS       |     2 |
|        23 |       2 | ParaSource_3 | NULL     |     3 |
+-----------+---------+--------------+----------+-------+

- SqlZim

Answer 2

这似乎可以实现你想要的功能：

SELECT TableCode, x.ColCode, v.*
FROM TableA a CROSS APPLY   
     (VALUES (1, 'ParaName-1', Col1, 1),
             (2, 'ParaName-2', Col2, 2),
             (3, 'ParaName-3', NULL, 2)
     ) v(ColCode, ColumnName, [Values], RowNo)
WHERE TableCode = 23;

我认为没有必要使用row_number()，因为你可以直接读取正确的值。此外，VALUES是SQL关键字，所以它是一个非常糟糕的列名。

- Gordon Linoff

---

这并没有帮助我，上面的查询没有给出我所需的输出，关于"ParaSource-1, ParaSource-2, ParaSource-3"怎么样？ - Vinayak Gurav