SQL 语法 : Create a single table with column names created from data stored over multiple tables

Question

我创建了下面的 SQL 脚本，它创建了四个表并将数据插入表中以演示我的问题(我希望这有帮助!)。

出现这个问题是因为最初这些数据存储在一个包含 400 列的表中，它变得非常难以管理，所以我想找到一种更好的方法来存储参数:

use master
GO
create database sptest
go

use sptest
go

CREATE TABLE JobFiles (
            [Id]       int PRIMARY KEY IDENTITY(0,1),
   [JobName]       nvarchar(256)  DEFAULT '',
   [CreateDate]     DateTime NOT NULL DEFAULT GETDATE(),
   [ModifyDate]     DateTime NOT NULL DEFAULT GETDATE(),
   [CreatedByUser]     nvarchar(64)  DEFAULT '',
   [ModifiedByUser]    nvarchar(64)  DEFAULT '')
GO


CREATE TABLE jpChar (
  [jpId] int PRIMARY KEY IDENTITY(0,1),
  [JobId] int REFERENCES JobFiles(Id),
  [jpName] varchar(64),
  [jpValue] nvarchar(255))


CREATE TABLE jpInt (
  [jpId] int PRIMARY KEY IDENTITY(0,1),
  [JobId] int REFERENCES JobFiles(Id),
  [jpName] varchar(64),
  [jpValue] int)

CREATE TABLE jpText (
  [jpId] int PRIMARY KEY IDENTITY(0,1),
  [JobId] int REFERENCES JobFiles(Id),
  [jpName] varchar(64),
  [jpValue] Text)


use spTest
go

    INSERT INTO JobFiles(JobName) VALUES ('File0')
    INSERT INTO JobFiles(JobName) VALUES ('File1')
    INSERT INTO JobFiles(JobName) VALUES ('File2')

    INSERT INTO jpChar(JobId,jpName, jpValue) VALUES (0, 'User', 'Paul')
    INSERT INTO jpChar(JobId,jpName, jpValue) VALUES (0, 'Dept', 'IT')
    INSERT INTO jpInt (JobId,jpName, jpValue) VALUES (0, 'Hours', '40')
    INSERT INTO jpText (JobId,jpName, jpValue) VALUES (0, 'Notes', 'Some Text')


    INSERT INTO jpChar(JobId,jpName, jpValue) VALUES (1, 'User', 'Bob')
    INSERT INTO jpChar(JobId,jpName, jpValue) VALUES (1, 'Dept', 'Sales')
    INSERT INTO jpInt (JobId,jpName, jpValue) VALUES (1, 'Hours', '20')
    INSERT INTO jpText (JobId,jpName, jpValue) VALUES (1, 'Notes', 'Some more Text')


    INSERT INTO jpChar(JobId,jpName, jpValue) VALUES (2, 'User', 'Jane')
    INSERT INTO jpChar(JobId,jpName, jpValue) VALUES (2, 'Dept', 'Support')


SELECT  JobFiles.Id, JobFiles.JobName,   
   jpChar.jpName AS cName, jpChar.jpValue AS cValue,
   jpInt.jpName AS iName, jpInt.jpValue AS iValue,
   jpText.jpName AS txtName, jpText.jpValue AS txtValue
FROM         JobFiles INNER JOIN
                      jpChar ON JobFiles.Id = jpChar.JobId LEFT JOIN
                      jpInt ON JobFiles.Id = jpInt.JobId LEFT JOIN
                      jpText ON JobFiles.Id = jpText.JobId

每个表中有数百个参数(以上只是少数)，它们都引用了 JobFiles 表中的一行。

当我运行上面的 SELECT 语句时，我得到了预期的以下结果:

id  JobName cName   cValue  iName   iValue  txtName txtValue
0   File0   User    Paul    Hours   40      Notes   Some Text
0   File0   Dept    IT      Hours   40      Notes   Some Text
1   File1   User    Bob     Hours   20      Notes   Some more Text
1   File1   Dept    Sales   Hours   20      Notes   Some more Text
2   File2   User    Jane    NULL    NULL    NULL    NULL
2   File2   Dept    Support NULL    NULL    NULL    NULL

我想要实现的是以不同方式排列数据以匹配原始 400 列表的外观:

将名为cName、iName、txtName的列的数据值返回到Columns中。将名为cValue、iValue、txtValue的列的数据值返回到行数据中。

即

id  JobName User   Dept    Hours   Notes
0   File0   Paul   IT      40      Some Text
1   File1   Bob    Sales   20      Some more Text 
2   File2   Jane   Support NULL    NULL
.     .       .       .      .       .

我不确定我将如何着手做这件事，希望得到任何建议和帮助？我还有其他问题，我将单独发布。

先谢谢你。

Answer 1

我可能会将“部门”记录分解到它们自己的表中，并相应地更改您的查询。

您遇到的麻烦是因为在您当前的数据库结构中，'User'/'Bob' 和 'Dept'/'IT' 之间没有区别