将唯一的属性集生成到新表中？

发布于 2024-09-30 14:38:59 字数 425 浏览 4 评论 0原文

我当前需要创建一个表来保存另一个表的唯一属性集。也就是说，我有一个如下所示的表：

Table A : columns = col 1, col2, col3, col4, col5

我需要生成这个：

Table B : columns = columnName, value
col 1, col1attribute1
col 1, col1attribute2
col 1, col1attribute3
col 2, col2attribute1
col 3, col3attribute1
col 3, col3attribute2

有没有一种简单的方法可以使用单独使用 (PL)SQL？如果我用 Java 以编程方式完成它，这似乎很简单，但理想情况下，如果可能的话，我想要一个仅 sql 的解决方案。

原文

I currently need to make a table that holds the unique sets of attributes for another table. That is, I have a table that looks like this:

Table A : columns = col 1, col2, col3, col4, col5

And I need to produce this:

Table B : columns = columnName, value
col 1, col1attribute1
col 1, col1attribute2
col 1, col1attribute3
col 2, col2attribute1
col 3, col3attribute1
col 3, col3attribute2

Is there an easy way to do this with (PL)SQL alone? It seems simple enough if I do it programatically in Java, but ideally I'd like an sql-only solution if possible.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

烟雨凡馨 2024-10-07 14:38:59

以下似乎有效：

CREATE TABLE TABLE_B (COLUMN_NAME, VALUE) AS
  SELECT DISTINCT 'COL_1', COL_1 FROM TABLE_A
  UNION ALL
  SELECT DISTINCT 'COL_2', COL_2 FROM TABLE_A
  UNION ALL
  SELECT DISTINCT 'COL_3', COL_3 FROM TABLE_A
  UNION ALL
  SELECT DISTINCT 'COL_4', COL_4 FROM TABLE_A
  UNION ALL
  SELECT DISTINCT 'COL_5', COL_5 FROM TABLE_A;

分享和享受。

The following seems to work:

CREATE TABLE TABLE_B (COLUMN_NAME, VALUE) AS
  SELECT DISTINCT 'COL_1', COL_1 FROM TABLE_A
  UNION ALL
  SELECT DISTINCT 'COL_2', COL_2 FROM TABLE_A
  UNION ALL
  SELECT DISTINCT 'COL_3', COL_3 FROM TABLE_A
  UNION ALL
  SELECT DISTINCT 'COL_4', COL_4 FROM TABLE_A
  UNION ALL
  SELECT DISTINCT 'COL_5', COL_5 FROM TABLE_A;

Share and enjoy.

回复收藏 0 原文

时间海 2024-10-07 14:38:59

另一种方法是使用：

  SELECT * INTO table_b from (
  SELECT DISTINCT 'COL_1', COL_1 FROM TABLE_A
  UNION ALL
  SELECT DISTINCT 'COL_2', COL_2 FROM TABLE_A
  UNION ALL
  SELECT DISTINCT 'COL_3', COL_3 FROM TABLE_A
  UNION ALL
  SELECT DISTINCT 'COL_4', COL_4 FROM TABLE_A
  UNION ALL
  SELECT DISTINCT 'COL_5', COL_5 FROM TABLE_A)a;

编辑：正如有人指出这在 postgres 中工作，（我认为 sql server 也是如此），从你的 PL/SQL 标签，我假设 postgres 作为 postegres 是 PL/pgSQL，没有注意到，直到现在。

Another way is to use:

  SELECT * INTO table_b from (
  SELECT DISTINCT 'COL_1', COL_1 FROM TABLE_A
  UNION ALL
  SELECT DISTINCT 'COL_2', COL_2 FROM TABLE_A
  UNION ALL
  SELECT DISTINCT 'COL_3', COL_3 FROM TABLE_A
  UNION ALL
  SELECT DISTINCT 'COL_4', COL_4 FROM TABLE_A
  UNION ALL
  SELECT DISTINCT 'COL_5', COL_5 FROM TABLE_A)a;

EDIT: as someone points out this works in postgres, (and I think sql server as well), from your PL/SQL tag, I assumed postgres as postegres is PL/pgSQL, hadn't noticed that until now.

回复收藏 0 原文

~没有更多了~