类似于行到列(EAV到关系)-请查询SQL [英] Similar to rows-to-col(EAV to relational) - SQL query please

问题描述

我的数据集与EAV格式不完全相同，但是有些相似.这是数据:

My dataset is not exactly like EAV format, but it's somewhat similar; here's the data:

我需要的格式如下:

对于每个EN_NO组，我都需要以上格式的数据.如果EN_NO上的组> 1，则重复产品密钥应转到重复产品列，否则应不进入(例如EN_NO 4和5).

For every EN_NO group I need the data in above format. If group on EN_NO > 1 then respestive product key should go to respestive product column otherwise not (for e.g. EN_NO 4 and 5).

我希望我清楚.数据在Qracle表中，请提出查询以获取所需格式的数据.

I hope I am clear. Data is in a Qracle table, please suggest a query to get the data in the format I need.

谢谢， 普拉卡什

推荐答案

我强烈建议更改您的表结构.目前，您将两个信息绑定到一个字段中.这是一个SQL反模式，破坏了Oracle用户进行某些优化的能力.

I would highly recommend altering your table structure. At present you have two pieces of information tied into a single field. This is a SQL Anti-Pattern and destroys the ability of Oracle to user certain optimisations.

相反，请考虑将"PROD_KEY"分为两个字段(PRODUCT_TYPE = Prod_A等)(SUB_PRODUCT_ID = 1、2、3等).或者，要在整个数据库中减少潜在更改，只需将PRODUCT_TYPE添加到当前表中即可.

Instead, please consider splitting "PROD_KEY" into two fields (PRODUCT_TYPE = Prod_A, etc) (SUB_PRODUCT_ID = 1, 2, 3, etc). Or, to cause less potential change across the database, simply add the PRODUCT_TYPE to your current table.

也就是说，使用您当前的结构...

That said, using your current structure...

SELECT
  EN_NO,
  PROD_KEY,
  CASE WHEN (EN_NO < 4) AND (LEFT(PROD_KEY, 6) = 'Prod_A') THEN PROD_KEY ELSE NULL END AS Prod_A,
  CASE WHEN (EN_NO < 4) AND (LEFT(PROD_KEY, 6) = 'Prod_B') THEN PROD_KEY ELSE NULL END AS Prod_B,
  CASE WHEN (EN_NO < 4) AND (LEFT(PROD_KEY, 6) = 'Prod_C') THEN PROD_KEY ELSE NULL END AS Prod_C,
  PROD_QTY
FROM
  yourTable

当您明确知道需要哪些列作为输出时，此方法有效.如果您需要代码来适应Prod_D等，那么您需要编写编写代码的代码(Dynamic SQL).

This works when you know specifically what columns you need as output. If you need the code to adapt to having Prod_D, etc, then you need to write code that writes code (Dynamic SQL).

这篇关于类似于行到列(EAV到关系)-请查询SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！