SQL - 根据行值创建自定义列

发布于 2025-01-12 05:08:34 字数 8 浏览 1 评论 0原文

continue

I have a below table with custom values. Id, productname and value can be anything. I want to read productname, create new column with that productname and put values belong to that productname under it.

Table:

ID	ProductName	Value
1	product1	111
1	product2	112
2	product1	221
2	product2	222
3	product1	331
1	product3	113

what I want:

ID	Product1	Product2	Product3
1	111	112	111
2	221	222
3	331

分享到QQ

分享到微博

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

发布评论

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

夏有森光若流苏 2025-01-19 05:08:34

因此 SQL 无法做的一件事就是为您提供任意数量的列。如果您认为执行查询分为两个步骤，则第一步通过读取预期的列数来设置查询的内存，第二步实际填充它。如果您的（假设的）查询具有任意数量的列，则在运行时之前它无法知道需要多少列。

话虽这么说，如果您提前知道需要多少列，则有几种方法可以做到这一点。在 postgresql 中，您将需要使用 crosstab() 函数。交叉表功能（有点像 Excel 中的数据透视表）。我并不完全熟悉它，所以我正在研究 this文档页面。请尝试以下操作：

SELECT *
FROM crosstab(
    'select Id,ProductName,value
     from base_table
     order by 1',
    'select distinct productName
     from base_table
     order by 1')
as ct(id, Product1, Product2, Product3);

注意：这只是最佳猜测。它可能不适合您的目的。

So one thing that SQL can't do is give you an arbitrary number of columns. If you think about executing a query as two steps, the first step sets up the memory for the query by reading how many columns to expect, and the second actually fills it. If your (hypothetical) query were to have an arbitrary number of columns, it wouldn't be able to tell how many columns it needed until runtime.

That being said, there are a few ways to do it if you know ahead of time how many columns you need. In postgresql, you're going to want to use the crosstab() function. Crosstab functions (kinda) like a pivot table in Excel. I'm not entirely familiar with it, so I'm working off this documentation page. Try the following:

SELECT *
FROM crosstab(
    'select Id,ProductName,value
     from base_table
     order by 1',
    'select distinct productName
     from base_table
     order by 1')
as ct(id, Product1, Product2, Product3);

Note: This is only a best-guess. It's likely not perfect for your purposes.

回复收藏 0 原文

~没有更多了~