SQL能够将行数据连接到列吗？ [英] SQL able to join row data to column?

问题描述

大家好，



我正在优化我的供应商提供的解决方案，这个解决方案已经使用多年了。因此，我无法更改计划如下的数据库架构：



表：分支

列：BranchName，BranchABBR





表：StockDetails

pID，sStoreID（直接映射到BranchName），s15Days





表：股票

pID，sOutletName（基于branchname示例创建：BranchName = Outlet1，将创建一个新列对于branchname：sOutlet1）





想问一下我是否可以加入'stockdetails'表到'stock'通过'StockDetails'中的'sStoreID'表到仅通过纯SQL查询在Stock表中的特定列？



我当前的查询如下所示我想要理想情况下绘制列如 c。（'s'+ A.BranchName）我知道它不起作用但我真的不知道如何从SQL本身动态绘制所需的列。

Hi all,

I'm in the midst of optimizing a solution provided by my vendor, this solution is live in use already for many years. Hence I am unable to alter the database schema which is planned as below:

Table: Branch
Column: BranchName, BranchABBR


Table: StockDetails
pID, sStoreID (which is mapped directly to BranchName), s15Days


Table: Stock
pID, sOutletName(created base on branchname example: BranchName = Outlet1, a new column will be created for the branchname: sOutlet1)


Would like to ask if there is anyway I can join the 'stockdetails' table to 'stock' table via the 'sStoreID' from 'StockDetails' to the specific column that is in Stock table through purely SQL query only?

My current query is as below and i want to ideally draw the column like c.('s' + A.BranchName) I know it would not work but I really have got no idea how do i dynamically draw the desired column from SQL itself.

SELECT *
FROM(
SELECT c.pID, A.BranchName, c.('s' + A.BranchName), b.s15Days, b.s30Days, b.s60Days
FROM Branch A
INNER JOIN StockDetails B
ON A.BranchName = B.sStoreID AND A.BranchActive = 1
INNER JOIN Stock C
ON B.pID = C.pID AND C.sDisable = 'No'
WHERE c.pID = 17705) A

因为我开始了g结果如下：

PID，BranchName，Outlet1，Outlet2，Outlet3，s15Days，s30Days，s60Days ..



我想要的结果如
PID，BranchName，Outlet1，S15days，s30Days，s60Days

PID，BranchName，Outlet2，S15days，s30Days，s60Days

PID， BranchName，Outlet3，S15days，s30Days，s60Days





和Outlet1，Outlet2，Outlet3是在'Stock'表中创建的列，它随着更多网点的增加，将会增加。因此我需要一个动态查询来相应地映射和显示列

As I am getting the result as below:
PID, BranchName, Outlet1, Outlet2, Outlet3, s15Days, s30Days, s60Days..

My desired results is as
PID, BranchName, Outlet1, S15days, s30Days, s60Days
PID, BranchName, Outlet2, S15days, s30Days, s60Days
PID, BranchName, Outlet3, S15days, s30Days, s60Days


and Outlet1, Outlet2, Outlet3 are columns created in the 'Stock' table, it will increase as more outlets are added. hence I will need a dynamic query to map and display the columns accordingly

推荐答案

请阅读我对该问题的评论。



您可能正在尝试实现类似的目标：

Please, read my comment to the question.

You're, probably, trying to achieve something like that:

DECLARE @Branch TABLE(ID INT IDENTITY(1,1), BranchName VARCHAR(30))

INSERT INTO @Branch (BranchName)
SELECT 'Outlet1' AS BranchName
UNION ALL SELECT 'Outlet2' AS BranchName
UNION ALL SELECT 'Outlet3' AS BranchName
UNION ALL SELECT 'Outlet4' AS BranchName
UNION ALL SELECT 'Outlet5' AS BranchName


SELECT 's' + BranchName AS NewOutletName
FROM @Branch

结果：

Result:

NewOutletName
sOutlet1
sOutlet2
sOutlet3
sOutlet4
sOutlet5

这篇关于SQL能够将行数据连接到列吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！